Bài giảng Truy vấn trong Access - Trần Văn Nghi

p thứ tự các Record theo DLcột:	 Sort = Ascending / DescendingEx: Hiển thị danh sách sinh viên của các khoa5.1 qbe – select Q. chọn cộtEx: Hiển thị danh sách sinh viên của các khoa5.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ọc5.1 QBE – select q chọn dòng Ex: Hiển thị điểm CSDL và ACCB của SV tin học5.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 NULL5.1 QBE – hàm & tt điều kiện Hiển thị danh sách SV khoa Tin Học sinh trong thập niên 90 5.2 QBE – thống kê toàn phần1. 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 = WhereEx: 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 ACCB5.2 QBE – thống kê toàn phầnEx: 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 ACCB5.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 đạt5.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 đạt5.3 qbe – crosstab query1. 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àyEx: 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 sinh5.3 qbe – crosstab queryEx: 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 sinh5.4 qBE – sub queryThường dùng để tạo tập trị cho biểu thức điều kiện: 	 = 	(Select From Where )	Các toán tử lấy giá trị:	IN (SELECT ) , 	NOT IN (SELECT )	> ALL (SELECT ) ,	.,.,.Tạo điều kiện cho cột b:	(điều kiện cho . ) = m	(điều kiện cho . ) = nHiển thị các SV cùng có cả 2 điểm ACCB và CSDL5.5 qbe – duplicate queryHiển thị các SV cùng có cả 2 điểm ACCB và CSDL5.6 qbe – unmatch queryTruy 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ệuTạo điều kiện cho cột cung cấp quan hệ:	NOT IN (Select 	 From 	 Where = [ Nhập trị cần kiểm tra: ] 	 = + [ Nhập trị so sánh: ] 6. tạo truy vấn bằng sqlCú pháp cơ bản:	SELECT , AS , 	FROM	 [],  	WHERE 	 AND 	[ GROUP BY ,  ]	[ HAVING ,  ]	[ 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ộtEx: Hiển thị danh sách sinh viên của các khoaSQL: hiển thị danh sach SV tại các khoaSELECT MaSV , HoTenSV, TenKhoaFROM HOSOSV H, DMLOP L, DMKHOA KWHERE H.MaLop = L.MaLopAND L.MaKhoa= K.MaKhoaORDER 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 KhoaSELECT MaSV , HoTenSV, TenKhoaFROM HOSOSV H, DMLOP L, DMKHOA KWHERE H.MaLop = L.MaLopAND L.MaKhoa= K.MaKhoaAND K.TenKhoa=“Tin Học”AND Year(NgaySinh) between 1990 and 1999ORDER 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 	 AND  ; 	Điều kiện : 		SQL: hiển thị tổng số SV có điểm đạt của môn ACCBSELECT Count(MaSV) as SoSVDatACCB FROM DIEMMHWHERE MaMH=“ACCB”AND Diem>=5;SQL: hiển thị điểm có hệ số lớn nhất của khóa họcSELECT Max(Diem*HeSo) as DiemHSMax FROM DMMonHoc M, DIEMMH DWHERE M.MaMH=D.MaMH ;6.2 SqL – thống kê nhóm dl	SELECT , 	 (tên cột) AS 	FROM	 [ [BD]	WHERE 	 AND  	GROUP BY ,  	HAVING 	 ; 	Đ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 khoaSELECT TenKhoa, Count(MaSV) as SoSVDatCSTH FROM HoSoSV H, DIEMMH D, DMLOP L, DMKHOA K WHERE H.MaSV=D.MaSVAND H.MaLop=L.MaLopAND L.MaKhoa=K.MaKhoaAND MaMH=“CSTH”AND Diem>=5GROUP 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ênSELECT MaMH, Count(MaSV) as SoSVDat FROM DIEMMHWHERE Diem >=5 GROUP BY MaMHHAVING Count(MaSV)>=20;6.3 sql – crosstab query	TRANSFORM (tên cột TK) as X	SELECT	,	FROM	 [BD1], [BD2]	WHERE	=	AND	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 sinh6.3 sql – crosstab querySQL: hiển thị số SV đạt theo lớp và theo môn họcTRANSFORM 	Count(H.MaSV) as XSELECT 	MaLop FROM 	HoSoSV H, DIEMMH D WHERE 	H.MaSV=D.MaSVAND 	Diem>=5GROUP BY 	MaLopPIVOT	 	MaMH;6.3 sql – crosstab querySQL: hiển thị số SV theo lớp, khoa và theo năm sinhTRANSFORM Count(H.MaSV)SELECT 	L.MaLop, K.TenKhoa FROM 	HOSOSV H, DMLOP L, DMKHOA KWHERE 	H.MaLop=L.MaLopAND	L.MaKhoa=K.MaKhoaGROUP BY 	L.MaLop, L.TenKhoaPIVOT	Year(NgaySinh);6.4 SqL – sub queryHiển thị điểm các SV có hộ khẩu tại Tp.HCMSelect MaSV, MaMH, Diem From DIEMMHWhere MaSV IN (Select MaSV From HOSOSV Where HoKhau Like “*HCM”);6.4 SqL – sub queryNVIEN (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ânSelect 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 queryTruy 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à CSDLSELECT 	X.MaSV, X.Diem as DiemACCB, 	Y.DIEM as DiemCSDLFROM 	DIEMMH X, DIEMMH YWHERE 	X.MaSV=Y.MaSVAND 	X.MaMH="ACCB"AND 	Y.MaMH="CSDL"AND 	X.Diem>=5AND 	Y.Diem>=5;6.6 sql – unmatch queryTruy 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. AND  	AND 	X. NOT IN 	(Select 	From 	Where )	;6.6 sql – unmatch querySQL: 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 DWHERE 	H.MaSV=D.MaSVAND	H.MaSV Like “??QT*”AND 	H.MaSV NOT IN 	(SELECT MaSV FROM DIEMMH WHERE 	MaMH=“ACCB”);6.6 sql – unmatch querySQL: hiển thị các SV chưa có điểm môn nào cả SELECT 	MaSV, HoTenSV, MaLop FROM 	HOSOSVWHERE 	MaSV NOT IN 	(SELECT MaSV FROM DIEMMH);6.7 sql – union queryTruy 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 HOADONUNIONSELECT “” ,	 SoPT,	NgayPT, 	 SoTienPT,	MaKH FROM PHIEUTHU;6.8 sql – action queryTruy 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  ];	Update DIEMMH Set Diem=Diem+1 	Where MaMH=“ACCB” And MaSV Like “??TH*”;Xóa Record :	DELETE FROM 	[WHERE  ];	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 queryThêm dữ liệu vào cuối bảng : 	INSER INTO 	 ( , ,  )	SELECT , . 	FROM ,.	WHERE AND  ;	Inser Into SVTPHCM MaSV, HoTenSV, Nu, HoKhau	Select MaSV, HoTenSV, Nu, HoKhau	From HoSoSV 	Where HoKhau Like “*HCM”;6.9 sql – parameter queryTruy 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 QuerySELECT H.MaSV, HoTenSV, DiemFROM HoSoSV H, DiemMH DWHERE H.MaSV=D.MaSVAND MaMH=[Nhập mã môn học cần xem: ]AND Diem>=[Nhập diểm nhỏ nhất cần xem: ];