Giáo trinh tin học - Truy vấn dữ liệu
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (253.33 KB, 23 trang )
3-1
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Bài 3
Truy vấn dữ liệu
Nội dung
Tổng quan về truy vấn dữ liệu 3
Thực hiện truy vấn bằng ngôn ngữ SQL 5
Giới thiệu công cụ truy vấn bằng QBE 15
Tham số trong truy vấn 2
0
Bài tập 23
3-2
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Giới thiệu
Lý thuyết
18 Tiết
- Thực hiện các truy vấn bằng ngôn ngữ SQL
Ý nghóa sử dụng của các loại truy vấn
Cú pháp SQL của từng loại
- Công cụ hỗ trợ tạo truy vấn của Microsoft Access: QBE
- Tham số của truy vấn
Thực hành
36 Tiết
- Sử dụng ngôn ngữ SQL và công cụ QBE để tạo các truy vấn
Truy vấn chọn dữ liệu
Truy vấn tạo bảng
Truy vấn thêm dữ liệu
Truy vấn xoá dữ liệu
Truy vấn cập nhật dữ liệu
Truy vấn thống kê
Truy vấn con
- Tạo truy vấn Crosstab bằng công cụ QBE
3-3
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Tổng quan về truy vấn dữ liệu
Nội dung
Trình bày các dạng truy vấn
và ý nghóa của từng dạng.
Phân loại truy vấn
Các công cụ tạo truy vấn trong Access
Sau khi tạo các bảng và lưu trữ dữ liệu trong bảng, người quản lý dữ liệu cần thực hiện những thao
tác liên quan đến nguồn dữ liệu đang được lưu trữ trong bảng. Các thao tác thông thường trên nguồn
dữ liệu là tìm kiếm thông tin, cập nhật nguồn dữ liệu, hiển thò dữ liệu ở những dạng khác nhau, ….
Để thực hiện được những thao tác đó, trong môi trường của hệ quản trò cơ sở dữ liệu ta sẽ sử dụng
ngôn ngữ truy vấn có cấu trúc (SQL) để tạo ra những câu truy vấn thao tác trên dữ liệu.
Các truy vấn được tạo trong môi trường của Access gọi là Query. Ta có thể tạo Query bằng 2 cách:
viết trực tiếp trong cửa sổ viết lệnh SQL hay là tạo bằng công cụ hỗ trợ QBE của Access.
Các loại truy vấn
Truy vấn chọn lựa (Select Query)
Loại truy vấn này chỉ mang tính chất cung cấp thông tin đã được lưu trữ trong bảng, không làm thay
đổi dữ liệu của bảng. Tập kết quả của truy vấn có thể được chọn lựa theo điều kiện cần xem thông
tin, có thể tạo ra các cột tính toán hay thống kê dữ liệu trên bảng khi hiển thò.
Tập dữ liệu của truy vấn hoàn toàn giống với những gì được lưu trong các bảng nhưng có thể hiển
thò gọn gàng và chứa nhiều thông tin không.
Ví dụ
Tru
y
vấn tìm các NV Tổ 1
3-4
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Truy vấn tạo bảng(Make-Table Query)
Loại truy vấn này hoàn toàn giống với Select Query về mặt cú pháp và tập dữ liệu kết quả. Điểm
khác biệt duy nhất ở chỗ tập kết quả của Make-Table Query là tạo ra một bảng mới chứa dữ liệu.
Bảng được tạo có cấu trúc và các mẫu tin giống với những field có trong câu lệnh SQL tạo ra truy
vấn.
Ví dụ
Truy vấn thêm dữ liệu (Append Query)
Thêm 1 hay nhiều dòng dữ liệu mới vào cuối một bảng. Mỗi câu truy vấn chỉ dùng để thêm dữ liệu
vào cho một bảng.
Truy vấn cập nhật (Update Query)
Dùng để thực hiện việc sửa đổi giá trò các field của một hay nhiều bảng.
Truy vấn xoá (Delete Query)
Thực hiện xoá 1 hay nhiều dòng dữ liệu của một bảng.
Truy vấn thống kê dữ liệu (Crosstab Query)
Thực hiện các truy vấn có tính chất thống kê, tính toán và hiển thò dữ liệu theo dạng dòng và cột.
Ví dụ
Thống kê lương tháng theo tổ
Tạo bản
g
lương
Tháng
7/2000
3-5
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Thực hiện truy vấn bằng ngôn ngữ SQL
Nội dung
SQL là ngôn ngữ dùng để làm
việc với các CSDL. Với SQL
chúng ta có thể thực hiện các
thao tác chọn dữ liệu hiển thò,
thêm, xoá dữ liệu có trong các
bảng, … Phần này sẽ trình bày
các dạng truy vấn và cách tạo
những truy vấn này bằng ngôn
ngữ SQL.
Cách tạo
Truy vấn chọn dữ liệu
Các toán tử và một số hàm thông dụng
Truy vấn tạo bảng
Truy vấn thêm dữ liệu
Truy vấn cập nhật dữ liệu
Truy vấn xoá
Cách tạo chung
Như đã trình bày ở phần trên, có nhiều dạng truy vấn với những tính năng khác nhau như truy vấn
chọn dữ liệu, truy vấn thêm dữ liệu, … Các dạng truy vấn này sẽ được tạo ra bằng các lệnh SQL
khác nhau nhưng các bước thực hiện với ngôn ngữ SQL thì hoàn toàn giống nhau.
Chọn thực đơn Insert Ỉ Query hoặc trên cửa sổ Database nhấn vào Queries và nhấn nút
New
Trên cửa sổ tiếp theo, chọn Design View
Đóng hộp thoại Show Table
Chọn thực đơn View Ỉ SQL View hoặc chọn trong biểu tượng đầu tiên trên thanh công
cụ
Truy vấn chọn dữ liệu (Select Query)
Thực hiện chọn lựa hoặc rút trích dữ liệu trên một số cột của một hoặc nhiều bảng thoả điều kiện
nào đó. Có thể tạo ra các cột tính toán từ dữ liệu lưu trong bảng, nhóm dữ liệu trên các bảng có tính
chất thống kê tổng hợp (Summary).
Dạng 1: Truy vấn dữ liệu từ 1 bảng
Cú pháp
lệnh SQL
SELECT [Tính chất] Biểu_thức 1[ AS tên_ cột1] , Biểu_thức 2 [AS
tên_cột 2] , ….
FROM Tên_Bảng
[WHERE Điều kiện ]
[ORDER BY Biểu_thức [ASC/DESC] , Biểu_thức 2 [ASC/DESC] ,… ];
Mô tả
Tính chất : gồm 1 số tính chất sau ALL | DISTINCT | DISTINCT ROW | TOP
<n>
Biểu_thức : là tên field, hoặc 1 công thức tính toán
Tên_cột : tiêu đề của biểu thức khi xem kết quả truy vấn
Điều kiện : dùng để lọc dữ liệu, chỉ những mẫu tin nào thoả điều kiện mới được
hiển thò trong tập kết quả
Ví dụ 1
Từ bảng SINHVIEN , hãy đưa ra các thông tin sau: mã sinh viên, họ tên, phái, năm
3-6
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
sinh, nơi sinh nhưng chỉ hiển thò những sinh viên có năm sinh < = 1977 và sắp xếp
theo năm sinh giảm dần.
SELECT masv, hosv & " " & tensv AS [Họ tên], nam AS [Phai],
Year(ngaysinh) AS [Năm sinh], noisinh
FROM SINHVIEN
WHERE Year(ngaysinh) <= 1977
ORDER BY Year(ngaysinh) DESC;
Ví dụ 2
Tạo truy vấn có yêu cầu giống với Ví dụ 1, nhưng chỉ đưa ra các sinh viên có năm
sinh lớn nhất và Phái là "Nam" nếu Nam=yes, ngược lại là "Nữ"
SELECT TOP 1 masv, [hosv] & " " & [tensv] AS [Họ tên], nam AS
[Phái], Year(ngaysinh)AS [Năm sinh], IIf(nam=yes, "Nam", "Nữ")
as [Phai],noisinh
FROM SINHVIEN
ORDER BY Year(ngaysinh) DESC;
Dạng 2: Truy vấn dữ liệu từ nhiều bảng
Cú pháp
lệnh SQL
SELECT [Tính chất] Biểu_thức 1[ AS tên_ cột1] , Biểu_thức 2 [AS
tên_cột 2] , ….
FROM (Table1 [T1]<PHÉP NỐI> Table2 [T2] ON <ĐIỀU KIỆN NỐI>)
<PHÉP NỐI> Table3 [T3] ON <ĐIỀU KIỆN NỐI>
WHERE …Điều kiện……………
ORDER BY Biểu_thức 1 [ASC/DESC] , Biểu_thức 2 [ASC/DESC] ;
Mô tả
PHÉP NỐI : là mối quan hệ giữa 2 bảng cần lấy dữ liệu, gồm các loại sau
INNER JOIN : Kết nối bằng, đưa ra dữ liệu chung cùng xuất hiện ở các bảng
LEFT JOIN : kết nối ưu tiên bảng bên trái, đưa ra tất cả thông tin ở bảng bên
trái
RIGHT JOIN : kết nối ưu tiên bảng bên trái, đưa ra tất cả thông tin ở bảng bên
trái
ĐIỀU KIỆN NỐI : là điều kiện kết nối giữa các bảng thông qua các field quan hệ
T1, T2, T3 : bí danh của bảng trong trường hợp tên bảng quá dài
Ví dụ 1
Tạo truy vấn đưa ra các thông tin của các sinh viên học khoa Anh Văn, thông tin
gồm: mã sinh viên, họ tên, phái (thể hiện Nam, Nữ), tên khoa
SELECT masv, [hosv] & " " & [tensv] AS [Họ tên], IIf(nam=yes,
"Nam", "Nữ") as [Phái],tenkh AS [Tên khoa]
FROM SINHVIEN sv inner join KHOA k ON sv.makh=k.makh
WHERE makh=”AV”;
Ví dụ 2
Tạo truy vấn đưa ra kết quả thi của những sinh viên của từng khoa, thông tin gồm:
Tên khoa, mã sinh viên, họ tên, tên môn, điểm. Nhưng chỉ đưa ra những sinh viên
Đậu (điểm >=5 )
SELECT tenkh as [Tên khoa], sv.masv, [hosv] & " " & [tensv] AS
[Họ tên], tenmh as [Tên môn], diem as Điểm
FROM(SINHVIEN sv inner join KHOA k ON sv.makh=k.makh) inner join
KETQUA kq ON sv.masv=kq.masv
WHERE diem >=5 ;
3-7
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Dạng 3: Truy vấn thống kê dữ liệu của một bảng
Cú pháp
lệnh SQL
SELECT bt_thống_kê_1[ AS tên_ cột1] ,
bt_thống_kê_2 [AS tên_cột 2] ,
FROM Tên_Table
WHERE …Điều kiện…lọc dữ liệu…………
Mô tả
Bt_thống_kê : là các biểu thức có sử dụng các hàm trong nhóm hàm tính toán
thống kê ( SUM, COUNT, MIN, MAX… )
Ví dụ 1
Tính tổng số sinh viên, tìm tuổi lớn nhất và nhỏ nhất của sinh viên, thông tin gồm:
Tổng số SV, Tuổi nhỏ nhất, Tuổi lớn nhất.
SELECT Count(masv) AS [Tổng số SV],
MIN(Year(Date())-Year(ngaysinh)) AS[Tuổi nhỏ nhất],
MAX(Year(Date())-Year(ngaysinh)) AS [Tuổi lớn nhất]
FROM SINHVIEN
Dạng 4: Truy vấn dữ liệu có phân nhóm
Cú pháp
lệnh SQL
SELECT [Tính chất] Bt_1, Bt_2, Bt_thống_kê_ 1[ AS tên_ cột1] ,
Bt_thống_kê_2 [AS tên_cột 2] ,
FROM (Table1 [T1]<PHÉP NỐI> Table2 [T2] ON <ĐIỀU KIỆN NỐI>)
<PHÉP NỐI> Table3 [T3] ON <ĐIỀU KIỆN NỐI>
[WHERE …Điều kiện cho các biểu thức ]
GROUP BY Biểu_thức_1, Biểu_thức_2
[HAVING …Điều kiện cho các biểu thức thống kê]
[ORDER BY ];
Mô tả
Bt_? : có thể là field, có thể là các biểu thức có sử dụng các hàm thông
thường nhưng không được sử dụng các hàm thống kê.
Bt_thống_kê_? : là các biểu thức có sử dụng các hàm trong nhóm hàm thống kê.
GROUP BY : phải nhóm tất cả các biểu thức không có sử dụng các hàm thống
kê.
HAVING : là điều kiện cho các biểu thức thống kê( hoặc các biểu thức khác)
Ví dụ 1
Tạo truy vấn tính tổng số sinh viên theo từng khoa, thông tin gồm : Mã khoa, Tên
khoa, Tổng số SV
SELECT sv.makh as [Mã khoa], tenkh as [Tên khoa], Count(masv) AS
[Tổng số SV]
FROM SINHVIEN sv inner join KHOA k ON sv.makh=k.makh
GROUP BY sv.makh, tenkh ;
Ví dụ 2
Giống Ví dụ 1, nhưng hãy đưa ra tất cả các khoa kể cả những khoa không có sinh
viên.
SELECT k.makh as [Mã khoa], tenkh as [Tên khoa], Count(masv) AS
[Tổng số SV]
FROM SINHVIEN sv right join KHOA k ON sv.makh=k.makh
GROUP BY k.makh, tenkh ;
3-8
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Các toán tử và một số hàm thông dụng
Các toán tử
+,-, *, / : Các phép toán thông thường
\ : chia lấy phần nguyên
Mod : chia lấy phần dư
& : toán tử nối chuỗi
=, >, >=, <, <=, <> : các phép so sánh
Like : so sánh gần đúng (áp dụng cho chuỗi)
And, Or, Not : các phép toán Logic.
Nhóm hàm xử lý chuỗi
Hàm LEFT
Cú pháp
LEFT(chuỗi, n ký tự )
Mô tả
Hàm lấy n ký tự từ bên trái chuỗi
Hàm RIGHT
Cú pháp
RIGHT(chuỗi, n ký tự)
Mô tả
Hàm lấy n ký tự từ bên phải chuỗi
Hàm MID
Cú pháp
MID(chuỗi, vò trí bắt đầu, n ký tự )
Mô tả
Hàm lấy một chuỗi con có chiều dài n ký tự tại vò trí bắt đầu của chuỗi
Hàm TRIM
Cú pháp
TRIM(chuỗi)
Mô tả
Hàm dùng để bỏ những khoảng trắng ở bên trái và bên phải của chuỗi
Hàm RTRIM
Cú pháp
RTRIM(chuỗi)
Mô tả
Hàm dùng để bỏ những khoảng trắng ở bên phải của chuỗi
Hàm LTRIM
Cú pháp
LTRIM(chuỗi)
Mô tả
Hàm dùng để bỏ những khoảng trắng ở bên trái của chuỗi
Hàm UCASE
Cú pháp
UCASE(chuỗi)
Mô tả
Hàm chuyển đổi chữ thường thành chữ HOA
3-9
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Hàm LCASE
Cú pháp
LCASE(chuỗi)
Mô tả
Hàm chuyển đổi chữ HOA thành chữ thường
Hàm điều kiện
Hàm IIF
Cú pháp
IIF(Biểu thức điều kiện, Giá trò 1,Giá trò 2)
Mô tả
Biểu thức điều kiện: là một biểu thức Logic, trả về giá trò TRUE/ FALSE
Giá trò 1: là giá trò trả về của hàm trong trường hợp biểu thức điều kiện đúng
Giá trò 2: là giá trò trả về của hàm trong trường hợp biểu thức điều kiện sai
Nhóm hàm xử lý thời gian
Hàm DATE
Cú pháp
DATE()
Mô tả
Hàm trả về ngày/ tháng/năm hiện hành
Hàm DAY
Cú pháp
DAY(Biểu thức ngày)
Mô tả
Hàm trả về ngày trong biểu thức
Hàm MONTH
Cú pháp
MONTH(Biểu thức ngày)
Mô tả
Hàm trả về tháng trong biểu thức
Hàm YEAR
Cú pháp
MONTH(Biểu thức ngày)
Mô tả
Hàm trả về năm trong biểu thức
Nhóm hàm tính toán thống kê
Hàm COUNT
Cú pháp
COUNT(biểu thức)
Mô tả
Hàm thống kê đếm giá trò của biểu thức.
Biểu thức có thể là một cột hoặc là 1 công thức
3-10
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Hàm SUM
Cú pháp
SUM (biểu thức)
Mô tả
Hàm tính tổng của biểu thức.
Biểu thức có thể là một cột hoặc là 1 công thức
Hàm MIN
Cú pháp
MIN (biểu thức)
Mô tả
Hàm tìm giá trò nhỏ nhất của biểu thức.
Biểu thức có thể là một cột hoặc là 1 công thức
Hàm MAX
Cú pháp
MAX (biểu thức)
Mô tả
Hàm tìm giá trò lớn nhất của biểu thức.
Biểu thức có thể là một cột hoặc là 1 công thức
Hàm AVG
Cú pháp
AVG (biểu thức)
Mô tả
Hàm tính giá trò trung bình của biểu thức.
Biểu thức có thể là một cột hoặc là 1 công thức
Truy vấn tạo bảng (Make-Table Query)
Thực hiện rút trích dữ liệu của một hoặc nhiều bảng khác nhau, sau đó sao chép kết quả thực hiện
ra 1 bảng khác có cấu trúc và dữ liệu là những field được chỉ đònh trong câu truy vấn.
Cú pháp
lệnh SQL
SELECT [Tính chất] Bt_1, Bt_2, Bt_thống_kê_ 1[ AS tên_ cột1] ,
Bt_thống_kê_2 [AS tên_cột 2] ,
INTO Tên_Table_mới
FROM (Table1 [T1]<PHÉP NỐI> Table2 [T2] ON <ĐIỀU KIỆN NỐI>)
<PHÉP NỐI> Table3 [T3] ON <ĐIỀU KIỆN NỐI>
[WHERE ]
[GROUP BY ]
[HAVING … ]
[ORDER BY ]
Mô tả
INTO Tên_Table_Mới : tạo ra 1 bảng với tên cần sử dụng
Ví dụ 1
Từ bảng SINHVIEN hãy tạo ra bảng “SV co hoc bong” chứa tất cả thông tin của các
sinh có học bổng .
SELECT * INTO [SV CO HOC BONG]
FROM SINHVIEN
WHERE ISNULL(hocbong)=False;
Ví dụ 2
Hãy tạo ra bảng “Thong ke ket qua thi“ để thống kê kết quả thi theo môn kể cả
3-11
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
những môn không có SV thi, thông tin gồm : Mamon, Tenmh, Số SV đậu, Số SV rớt.
Biết rằng nếu điểm >= 5 thì Đậu, ngược lại là Rớt.
SELECT mh.mamh, tenmh, SUM(IIF(diem>=5,1,0)) as [Số SV đậu],
SUM(IIF(diem<5,1,0)) as [Số SV rớt]
FROM MONHOC mh left join KETQUA kq ON mh.mamh=kq.mamh
GROUP BY mh.mamh, tenmh;
Ghi chú Cú pháp của Make-Table Query hoàn toàn tương tự với Select Query, chỉ khác là sau khi
kết thúc mệnh đề SELECT ta có câu lệnh : INTO Tên_Table_mới
Truy vấn thêm dữ liệu (Append Query)
Thực hiện việc thêm dữ liệu vào cuối 1 bảng. Dữ liệu thêm vào bảng có thể cung cấp trực tiếp hay
lấy từ những bảng khác
Dạng 1: Thêm 1 dòng dữ liệu trực tiếp
Cú pháp
lệnh SQL
INSERT INTO Tên_Bảng( field1, field2,…….)
VALUES ( giá trò 1, giá trò 2, )
Mô tả
Tên_Bảng : tên của bảng được thêm dữ liệu
giá trò 1, giá trò 2, : các giá trò cần thêm tương ứng với field1, field2 ,… trong
bảng được thêm
Ví dụ
Thêm 1 môn học mới vào bảng MONHOC với các giá trò sau: Mã môn học là 10,
Tên môn học là Cấu trúc dữ liệu
INSERT INTO MONHOC (mamh, tenmh)
VALUES (‘10’, ‘Cấu trúc dữ liệu’);
Ghi chú Trật tự của các giá trò thêm vào bảng phải tương ứng với trình tự của các field được
liệt kê phía sau Tên_Bảng và phải đảm bảo đúng kiểu dữ liệu.
Dạng 2: Thêm nhiều dòng, dữ liệu được lấy từ các bảng khác
Cú pháp
lệnh SQL
INSERT INTO Tên_Bảng_Đích ( field1, field2, )
SELECT [Tính chất] Bt_1, Bt_2,…
FROM (Table1 [T1]<PHÉP NỐI> Table2 [T2] ON <ĐIỀU KIỆN NỐI>)
<PHÉP NỐI> Table3 [T3] ON <ĐIỀU KIỆN NỐI>
[WHERE ]
[ORDER BY ]
Mô tả
Tên_Bảng_Đích : là tên bảng được thêm dữ liệu
FROM : gồm các bảng nguồn dùng cho việc lấy dữ liệu
Bt_? : có thể là các giá trò cụ thể, tên field hay được tạo thành từ công
3-12
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
thực tính toán giữa những field có trong các bảng dữ liệu nguồn.
Ví dụ 1
Thêm vào bảng KETQUA danh sách các sinh viên có mã khoa là AV học môn có
mã số là “09” và điểm ban đầu là 0 .
INSERT INTO KETQUA (mamh, masv, diem)
SELECT “09”, masv, 0
FROM SINHVIEN
WHERE makh= ‘AV’ ;
Ghi chú Đối với dạng Thêm nhiều dòng dữ liệu được lấy từ các bảng nguồn, phần phía sau khi kết
thúc mệnh đề INSERT INTO là 1 câu lệnh có cấu trúc hoàn toàn tương tự với Select Query và
thông tin chọn lọc ra phải tương ứng với các field cần thêm trong Bảng Đích
Truy vấn cập nhật dữ liệu (Update Query)
Thực hiện việc sửa đổi đồng loạt các giá trò cho các cột trên nhiều dòng khác nhau trong bảng.
Cú pháp
lệnh SQL
UPDATE Bảng cập nhật
SET Field1=biểu thức1, Field2=biểu thức 2,….
WHERE Điều kiện cập nhật
Mô tả
UPDATE: tên bảng cần cập nhật, nếu có nhiều bảng liên quan nhau khi cập nhật thì
mệnh đề này giống như mệnh đề From trong Select Query, đưa ra quan
hệ giữa các bảng có liên quan đến việc cập nhật
SET : Cập nhật giá trò mới cho các field trên bảng dữ liệu
WHERE : Chỉ cập nhật dòng dữ liệu thoả điều kiện
Ví dụ
Tăng mức học bổng thêm 50000 đối với các sinh viên khoa Anh văn đã được cấp
học bổng.
UPDATE SINHVIEN
SET hocbong=hocbong+ 50000
WHERE makh=’AV’ and not IsNull(hocbong);
Ghi chú Nếu trong quan hệ Một-Nhiều, chúng ta chọn qui tắc ràng buộc tự động cập nhật các cột
quan hệ (Cascade Update Related Fields) thì khi sửa đổi dữ liệu bên nhánh Một, MS Access sẽ sửa
đổi theo giá trò mới bên nhánh Nhiều. Ngược lại chúng ta không thể sửa đổi được dữ liệu bên nhánh
Một nếu trong quan hệ chúng ta không chọn qui tắc ràng buộc tự động cập nhật các cột quan hệ
(Cascade Update Related Fields)
3-13
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Truy vấn xoá dữ liệu (Delete Query)
Thực hiện việc xoá một hay nhiều dòng trong các bảng thoả điều kiện muốn xoá.
Cú pháp
lệnh SQL
DELETE Tên_Table.*
FROM (Table1 [T1]<PHÉP NỐI> Table2 [T2] ON <ĐIỀU KIỆN NỐI>)
<PHÉP NỐI> Table3 [T3] ON <ĐIỀU KIỆN NỐI>
WHERE Điều kiện xoá
Mô tả
Tên_Table : là tên bảng sẽ bò xoá dữ liệu.
FROM : giống mệnh đề from trong Select query, đưa ra các bảng liên quan
trong việc xoá dữ liệu
WHERE : chỉ xoá dữ liệu khi thoả điều kiện xoá
Ví dụ
Hãy xoá môn học có mã số là “10” ra khỏi bảng MONHOC.
DELETE mh.*
FROM MONHOC mh
WHERE mamh=’10’ ;
Ghi chú Nếu trong quan hệ Một-Nhiều, chúng ta chọn qui tắc ràng buộc tự động xoá các dòng quan
hệ (Cascade Delete Related Records) khi xoá dữ liệu bên nhánh Một, MS Access sẽ xoá toàn bộ dữ
liệu có quan hệ bên nhánh Nhiều. Ngược lại chúng ta không thể xoá được dữ liệu bên nhánh Nhiều
khi chưa chọn qui tắc ràng buộc tự động xoá các dòng quan hệ
Truy vấn con
Ý nghóa cà cú pháp hoàn toàn giống với Select Query nhưng trong phần điều kiện lọc của nó có thể
chứa những câu Select Query khác.
Truy vấn con thường được sử dụng trong những yêu cầu hiển thò dữ liệu mà điều kiện lọc là một tập
kết quả lấy từ bảng, không phải là những giá trò cụ thể.
Ví dụ
Cho biết danh sách họ tên các sinh viên có học bổng cao nhất trong khoa Anh văn.
Cách giải quyết khi không thực hiện bằng truy vấn con
Tạo truy vấn thứ nhất để tìm ra học bổng cao nhất của khoa Anh văn
Tạo tiếp truy vấn thứ hai chọn ra các sinh viên có học bổng bằng với học bổng cao nhất mà
chúng ta đã tìm ra ở truy vấn thứ nhất và đồng thời sinh viên đó phải học ở khoa Anh văn.
Q_1
SELECT makh, max(hocbong) as hbcn_av
FROM SINHVIEN
WHERE makh=’AV’
GROUP BY makh ;
Q_2
SELECT sv.*
FROM SINHVIEN sv inner join Q_1 on sv.makh=Q_1.makh
and sv.hocbong=Q_1.hbcn_av;
3-14
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Cách giải quyết khi thực hiện bằng truy vấn con
Q_sub
SELECT sv. *
FROM SINHVIEN sv
WHERE makh=’AV’ and hocbong in ( SELECT max(hocbong)
FROM SINHVIEN
WHERE makh=’AV’ );
Trình tự thực hiện các lệnh trong một truy vấn con
Truy vấn bên trong cặp dấu ngoặc đơn ở cấp thấp nhất sẽ được thực hiện trước tiên. Theo
ví dụ trên, truy vấn tìm ra giá trò học bổng cao nhất của khoa Anh văn sẽ được thực hiện
trước.
Sau đó lấy kết quả của truy vấn con này để thực hiện việc so sánh với truy vấn cha ở bên
ngoài.
Những lưu ý khi viết truy vấn con
Câu Select Query trong biểu thức điều kiện lọc chỉ trả về một cột giá trò, có thể có nhiều
dòng
Biểu thức điều kiện so sánh với tập kết quả trả về từ câu Select bên trong thường là những
phép toán so sánh trên tập hợp như: IN, NOT IN
Những phép toán so sánh =, <, >, cũng có thể được sử dụng nhưng phải có từ khoá All
theo sau trong trường hợp tập kết quả có nhiều hơn một dòng. Trong trường hợp ví dụ trên ta
có thể sử dụng điều kiện như sau: hocbong >= All (Select )
3-15
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Giới thiệu công cụ truy vấn bằng QBE
Nội dung
Cách tạo truy vấn bằng công
cụ QBE của Access.
Giới thiệu công cụ QBE
Các thành phần trong màn hình QBE
Một số thao tác cơ bản để tạo truy vấn bằng QBE
Tạo truy vấn Crosstab bằng công cụ QBE
Bên cạnh việc tạo các loại truy vấn bằng cách tự viết bằng SQL, Microsoft Access cung cấp cho
chúng ta một công cụ đơn giản để tạo 1 truy vấn mà không cần phải biết cú pháp SQL của loại truy
cần tạo.
Giới thiệu công cụ QBE
QBE (Query By Examples) là một công cụ đồ hoạ giúp tạo nhanh các truy vấn dữ liệu. Người sử
dụng QBE có thể không biết gì về SQL nhưng vẫn có thể tạo được hầu kết những loại truy vấn thao
tác trên dữ liệu đang lưu trữ trong các bảng.
Microsoft Access cho phép di chuyển qua lại giữa cửa sổ làm việc QBE và cửa sổ viết lệnh SQL. Ta
có thể xem cú pháp SQL sau khi tạo truy vấn bằng QBE và ngược lại sau khi viết lệnh SQL ta có
thể xem cách trình bày lệnh đó bằng cửa sổ QBE.
Các thành phần trong màn hình QBE
Trong màn hình QBE có các thành phần sau:
Vùng chứa các bảng
Nơi chứa các bảng dữ liệu nguồn cho một truy vấn (tương đương với mệnh đề FROM trong câu lệnh
SQL )
Vùng lưới QBE
Lưới QBE được tổ chức theo dòng và cột. Mội cột dùng để chứa một field hay một biểu thức tính
toán. Mỗi dòng sẽ có những ý nghóa khác nhau tuỳ theo loại query cần tạo.
Chi tiết các dòng trong vùng lưới QBE gồm có:
Dòng Ýnghóa
Field Thể hiện các cột, biểu thức trong truy vấn
Table Tên bảng tương ứng của cột
Sort Cho phép sắp xếp thứ tự cột tăng hay giảm
Show Hiện hoặc ẩn cột
Criteria Chứa giá trò hay biểu thức lọc ứng với cột.
Nếu ghi các điều kiện trong cùng dòng thì các điều kiện sẽ nối với nhau bằng phép
AND, ngược lại nếu ghi điều kiện khác dòng thì phép OR sẽ được sử dụng (giống
như điều kiện lọc dữ liệu trong mệnh đề Where của cú pháp SQL)
3-16
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Một số thao tác cơ bản để tạo truy vấn bằng QBE
Tạo mới 1 truy vấn chọn lựa
Chọn thực đơn Insert Ỉ Quer Design View, chọn nút OK
Chọn các bảng dữ liệu nguồn của truy vấn: chọn tên bảng và nhấn nút Add trong hộp thoại
Show Table, chọn Close để đóng Show Table
Bằng thao tác Kéo-thả (Drag-Drop) field từ bảng vào lưới QBE để chọn những field cần
hiển thò trong tập kết quả
Ví dụ
Xem trước kết quả truy vấn hoặc thực hiện truy vấn
Muốn xem trước kết quả của truy vấn chúng ta chuyển sang chế độ Datasheet View như sau
Trên thực đơn chọn View Ỉ Datasheet View hoặc nhấn
trên thanh công cụ
Thực hiện câu truy vấn bằng cách :
Query Ỉ Run hoặc nhấn
trên thanh công cụ
Thêm hoặc xoá bảng khi tạo truy vấn bằng QBE
Thêm bảng vào truy vấn
Query Ỉ Add Table
Trong hộp thoại Show Table chọn tên bảng muốn đưa vào truy vấn, sau đó nhấn nút Add.
Nhấn nút Close khi muốn đóng hộp thoại này.
Xoá bảng ra khỏi truy vấn:
Chọn bảng muốn xoá trong vùng chứa bảng > nhấn phím Delelte
Tạo các cột tính toán hoặc các biểu thức trong truy vấn
Trong 1 truy vấn ngoài việc thể hiện các cột dữ liệu đã có trên bảng, ta còn có thể tạo các cột tính
toán thông qua các dữ liệu đã có trên bảng.
Có thể sử dụng các toán tử +,-,*, / hoặc các hàm xử lý chuỗi, xử lý thời gian……
Cách đặt tên mới cho 1 cột theo cú pháp :
Tên mới : tên cột hay biểu thức tính
3-17
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Ví dụ
Đặt tên cho cột khi cột là một biểu thức
Một số thao tác với dòng và cột trong vùng lưới QBE
Ẩn hay hiện dòng Table của lưới QBE
Dòng Table trong lưới QBE chứa tên bảng của field đang chọn trên cùng cột. Ta có thể ẩn dòng
này khi không quan tâm các field thuộc bảng nào
Ta có thể ẩn hoặc hiện dòng này thông qua chức năng View Ỉ Table Names.
Ẩn hay hiện dòng Total
Để thực hiện các thao tác tính toán thống kê trên các cột ta có thể thực hiện thông qua dòng
Total của lưới QBE, mặc đònh dòng này sẽ không có trên lưới.
Dòng Total cung cấp những hàm tính toán thống kê như Max, Min, Sum, Count, đồng thời chỉ
đònh cột nào sẽ được nhóm khi tính toán.
Ta có thể ẩn hoặc hiện dòng này thông qua chức năng View Ỉ Totals, hay có thể click vào biểu
tượng
trên thanh công cụ
Chèn thêm cột vào vùng lưới QBE
Trong quá trình tạo truy vấn, có thể chèn vào 1 cột mới nằm trước cột hiện hành, hoặc trong
vùng lưới QBE không còn cột nào.
Ta có thể chèn thêm 1 cột bằng cách Insert Ỉ Columns
Xoá cột trong vùng lưới QBE
Nếu muốn bỏ một cột trong lưới ta chỉ cần xoá nội dung của dòng field tại cột cần bỏ
3-18
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Tạo truy vấn Crosstab bằng QBE
Khi cần thống kê thông tin theo dạng dòng cột, ta có thể sử dụng truy vấn dạng Crosstab.
Ví dụ
Dữ liệu trong bảng CHAM CONG Kết quả mong muốn
Khi ta tạo truy vấn dạng này, phải có ít nhất là 3 cột:
Cột thể hiện dữ liệu dòng: Mã nhân viên (A01,B02,…)
Cột thể hiện dữ liệu cột: Tháng năm chấm công (06/2000, 07/2000)
Cột thể hiện giá trò tính toán: Tính tổng tiền công theo từng tháng của từng
nhân viên.
Cách thành phần tạo nên truy vấn Crosstab
Các dòng trong lưới QBE của truy vấn Crosstab ngoài các thành phần giống như dạng truy vấn chọn
lựa ta còn có các thành phần đặc trưng cho loại truy vấn này như sau:
Dòng Crosstab : chứa các giá trò sau
Row Heading: đối với các cột thể hiện dữ liệu theo dòng (ví dụ: Manv)
Column Heading: đối với cột thể hiện dữ liệu theo cột (ví dụ: ThangNam)
Value : đối với cột thể hiện giá trò cần thống kê.
(Not shown): đối với những cột chỉ đóng vai trò làm điều kiện thống kê và không thể hiện
ra ngoài.
Dòng Total: dùng để gom nhóm dữ liệu hoặc thực hiện tính toán thống kê, với các loại sau:
Group by: đối cột đóng vai trò là Column Heading (ví dụ: ThangNam), RowHeading
Các hàm thống kê: đối với cột đóng vai trò là Value, RowHeading
Cách tạo một truy vấn Crosstab
Chọn thực đơn Insert Ỉ Query, chọn Design View
Trên Show Table, chọn các bảng cần làm nguồn dữ liệu để thống kê, nhấn nút Add để thêm
bảng, nhấn Close đóng hộp thoại Show Table.
Chọn thực đơn InsertỈ Crosstab Query
Xác đònh cột thể hiện dữ liệu theo cột. Trong một truy vấn Crosstab, chỉ có duy nhất một cột
thể hiện dữ liệu theo cột (Column Heading)
Xác đònh các cột thể hiện dữ liệu theo dòng. Trong một truy vấn Crosstab, phải có ít nhất
một cột thể hiện dữ liệu theo dòng (Row Heading)
Xác đònh cột thể hiện giá trò cần thống kê. Trong một truy vấn Crosstab, chỉ có duy nhất một
cột thể hiện giá trò cần thống kê (Value)
3-19
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Ví
dụ
3-20
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Tham số trong truy vấn
Nội dung
Sử dụng truy vấn có tham số.
Ý nghóa của tham số
Các bước thực hiện tạo tham số cho truy vấn
Có những trường hợp, chúng ta cần tạo ra những truy vấn mang tính chất “động” nghóa là giá trò làm
điều kiện cho truy vấn có thể thay đổi được, lúc này ta cần tạo tham số cho truy vấn.
Ý nghóa của tham số
Tham số trong dùng để tạo ra các truy vấn theo một điều kiện “động” nào đó. Với điều kiện “động”
là giá trò sẽ được người sử dụng nhập vào khi cho thực hiện truy vấn.
Ví dụ
Thể hiện thông tin của tất cả sinh viên thuộc một khoa bất kỳ, khi thực hiện truy vấn
người sử dụng sẽ nhập vào mã khoa tương ứng muốn xem.
Với ví dụ trên, ta nhận thấy truy vấn này sẽ có tham số vào là một mã khoa (có kiểu dữ liệu chuỗi)
và tham số này phải được đặt ở vùng điều kiện để so sánh (trong mệnh đề Where đối với SQL hay
ở dòng Criteria đối với QBE ) đối với cột mã khoa.
Các bước thực hiện tạo tham số cho truy vấn
Việc khai báo và sử dụng tham số được thực hiện qua hai dạng tương ứng với hai công cụ truy vấn
mà Access cung cấp, đó là bằng cách viết lệnh trong cửa sổ SQL hoặc sử dụng Query Parameters
trong QBE. Dưới đây là các cách khai báo và sử dụng tham số
Khai báo và sử dụng tham số bằng công cụ QBE
Việc khai báo và sử dụng tham số trong QBE khá đơn giản, cụ thể là chúng ta sẽ thực hiện như sau:
Chọn thực đơh Query Ỉ Parameters…
Ghi tên tham số, chọn kiểu dữ liệu thích hợp cho tham số. Kiểu dữ liệu này phải bắt buộc
giống với kiểu dữ liệu của cột trong bảng mà ta cần so sánh.
Ví dụ
Trong ví dụ trên, chúng ta sẽ tạo ra một tham số có tên P_MaKhoa có kiểu dữ liệu là
chuỗi dùng để chứa giá trò mã khoa mà người dùng sẽ nhập giá trò vào cho truy vấn.
3-21
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Tên của tham số này sẽ được ghi vào dòng điều kiện (Criteria) trong màn hình QBE và tại
cột dữ liệu mà chúng ta muốn so sánh và phải nằm trong cặp ngoặc vuông([ ]).
Ví dụ
Khi cho thực hiện truy vấn, với mỗi tham số sẽ xuất hiện hộp thoại yêu cầu người sử dụng
cung cấp giá trò cho tham số tương ứng.
Ví dụ
Nhập vào giá trò cho tham số là ‘AV’ khi ta muốn truy vấn đưa ra thông tin của tất cả
các sinh viên thuộc khoa Anh văn.
Khai báo và sử dụng tham số bằng lệnh SQL
Việc khai báo và sử dụng tham số bằng cách viết lệnh SQL được thực hiện thông qua cú pháp lệnh,
đòi hỏi người viết phải nắm được các kiểu dữ liệu để khai báo cho phù hợp và sau đó kết hợp với
các câu lệnh SQL thông thường.
Cú pháp
lệnh SQL
PARAMETERS Tên_TS1 Kiểu_dữ_liệu, Tên_TS2 Kiểu_dữ_liệu, ;
SELECT | UPDATE | INSERT INTO | DELETE |
FROM | SET |
WHERE bt1 = [Tên_TS1] AND |OR bt2=[Tên_TS2]
Mô tả
PARAMETERS: Mệnh đề dùng để khai báo tham số, kết thúc là dấu chấm phầy (;)
trước khi viết qua các lệnh cho các dạng truy vấn.
Kiễu_dữ liệu: là các kiểu dữ liệu khai báo cho tham số tương ứng với kiểu dữ liệu
của cột. Với một số kiểu dữ liệu cột thường gặp, ta có các kiểu dữ liệu khai báo cho
tham số như sau:
3-22
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Kiểu dữ liệu cột Kiểu dữ liệu khai báo cho tham số
Yes/No Bit
Byte Byte
Integer Short
LongInteger Long
Currency Currency
Single IEEESingle
Double IEEEDouble
Date/Time DateTime
Binary Binary
Text Text ( 255 )
OLE Object LongBinary
Memo Text
Ví dụ
Với ví dụ đã giới thiệu ban đầu, ta thực hiện như sau :
PARAMETERS P_MaKhoa Text ( 2 ) ;
SELECT SINHVIEN.*, SINHVIEN.MAKH
FROM SINHVIEN
WHERE SINHVIEN.MAKH =[P_MaKhoa] ;
3-23
Tài liệu giảng dạy Microsoft Access 1 Trung Tâm Tin Học-Trường ĐH KHTN TP. HCM
Bài tập
Sử dụng cở sở dữ liệu đã tạo trong bài 2, Quản lý sinh viên, thực hiện các câu truy vấn trong phần
Phục Lục - Bài tập thực hành
