Thiết kế cơ sở dữ liệu quan hệ - Phần II Ngôn ngữ truy vấn SQL

dụ : #12/2/2001# ; #1-Jan-94# ; Cú pháp của SQL (p.2)Cú pháp SQL – Các toán tử số học (Arithmetic Operations)Cú pháp của SQL (p.3)Toán tửÝ nghĩaVí dụKết quả+Cộng5 + 2#28/08/01# + 47#01/09/01#-Trừ#02/09/01# - 3#30/08/01#*Nhân5 * 210/Chia5 / 22.5\Chia nguyên5 \ 22^Lũy thừa5 ^ 225ModChia dư5 Mod 21Cú pháp SQL – Các toán tử so sánh (Comparative Operations)Cú pháp của SQL (p.4)Toán tửÝ nghĩaVí dụKết quảLớn hơn2 > 5False>=Lớn hơn hay bằng2 >= 5False=Bằng nhau2 = 5FalseKhác nhau2 5TrueCú pháp SQL – Các toán tử luận lý (Logical Operations)Cú pháp của SQL (p.5)Toán tửÝ nghĩaVí dụKết quảNotLuật phủ địnhNot (5 > 2)Not (2>5)FalseTrueAndLuật và(5>2) And (2>5)(5>2) And (5>4)FalseTrueOrLuật hay(5>2) Or (2>5)(2>5) Or (4>5)TrueFalseVí dụSELECT 	HO,TEN	FROM	SINHVIEN	WHERE	NOT(MASV = ‘SV01’)2. 	SELECT 	MASV,HO,TEN	FROM	SINHVIEN	WHERE	(DIEMTB >= 5) AND 	(DIEMTBÝ nghĩa :	_Trả về các value1 có dạng thức giống như Các ký tự đại diện dùng trong khuôn mẫu :* : đại diện cho tất cả ký tự bất kỳ? : đại diện cho một ký tự bất kỳ# : đại diện cho 1 ký tự số[A1,A2,] : đại diện cho 1 ký tự thuộc tập {A1, A2, }[A1 – A2] : đại diện cho 1 ký tự thuộc khoảng ký tự từ A1 đến A2Cú pháp của SQL (p.8)Các toán tử LIKE – Ví dụ	SELECT 	*	FROM	SINHVIEN	WHERE	TEN LIKE ‘*Hoa’Ý nghĩa : tìm tất cả sinh viên có từ Hoa trong phần cuối của tên, ví dụ : ‘Ngọc Thoa’, ‘Đào Hoa’, Cú pháp của SQL (p.9)Chọn tất cả các cột có trong quan hệCác toán tử LIKE – Ví dụ (t.t)	SELECT 	*	FROM	SINHVIEN	WHERE	MASV LIKE ’SV0[1-4]’Ý nghĩa : tìm tất cả sinh viên có mã sinh viên là SV01, SV02, SV03 hoặc SV04 Toán tử LIKE được sử dụng nhiều trong các truy vấn tìm kiếm dữ liệuCú pháp của SQL (p.10)Cú pháp SQL – Các hàm lập sẵnCú pháp chung : (Danh sách đối số)Hàm IIf Cú pháp : IIf(điều kiện,giá trị 1,giá trị 2)Ý nghĩa : Trả về giá trị 1 nếu điều kiện đúng, ngược lại, trả về giá trị 2.Ví dụ :	SELECT 	*	FROM	SINHVIEN	WHERE	DIEMTB >= IIF(GIOITINH=‘Nam’,6.5,6)Cú pháp của SQL (p.11)Hàm DateCú pháp : Date()Ý nghĩa : Trả về giá trị ngày giờ hiện tại của hệ thống.Ví dụ :	SELECT 	*	FROM	HOADON	WHERE	NGAYLAP >= (DATE()-5)Cú pháp của SQL (p.12)Hàm SumCú pháp : Sum()Ý nghĩa : Trả về tổng của các giá trị tương ứng với của tất cả các bộ có trong quan hệ thỏa điều kiện WHERE.Ví dụ :	SELECT 	Sum(GIATRI)	FROM	HOADON	WHERE	NGAYLAP >= (DATE()-5)Ý nghĩa : Trả về tổng giá trị của các hóa đơn có ngày lập trong vòng 6 ngày gần đây.Cú pháp của SQL (p.13)Hàm MaxCú pháp : Max()Ý nghĩa : Trả về giá trị lớn nhất trong các giá trị tương ứng với của các bộ có trong quan hệ thỏa điều kiện WHERE.Ví dụ :	SELECT 	Max(GIATRI)	FROM	HOADON	WHERE	NGAYLAP >= (DATE()-5)Ý nghĩa : Trả về giá trị lớn nhất trong các hóa đơn có ngày lập trong vòng 6 ngày gần đây.Cú pháp của SQL (p.14)Một số hàm khácDay() : trả về chỉ số của ngày trong .Ví dụ : Day(#12/2/2005#)  12Month() : trả về chỉ số của tháng trong .Year() : trả về chỉ số của năm trong .Len() : trả về độ dài của chuỗiCú pháp của SQL (p.15)Một số hàm khác (t.t)Ví dụ :	SELECT 	* 	FROM	 	SINHVIEN	WHERE	LEN(TEN) > 4Chr() : trả về ký tự có mã ASCII tương ứng.InStr(start,s1,s2) : trả về vị trí của chuỗi s2 trong chuỗi s1 kể từ vị trí start.LCase(s) : trả về giá trị chuỗi in thường của chuỗi sUCase(s) : trả về giá trị chuỗi in hoa của chuỗi sCú pháp của SQL (p.16)Một số hàm khác (t.t)Left(s,n) : trả về chuỗi gồm n ký tự bên trái của chuỗi s.Right(s,n) : trả về chuỗi gồm n ký tự bên phải của chuỗi s.Mid(s,i,n) : trả về chuỗi con của chuỗi s gồm n ký tự kể từ vị trí i.Nz(v1,v2) : trả về giá trị v1 nếu v1 khác Null, ngược lại trả về giá trị v2.Cú pháp của SQL (p.17)Một số hàm khác (t.t)Min() : trả về giá trị nhỏ nhất trong các giá trị tương ứng với của các bộ thỏa điều kiện WHERE có trong quan hệ.Avg() : trả về giá trị trung bình cộng của các giá trị tương ứng với của các bộ thỏa điều kiện WHERE có trong quan hệ.Count() : trả về số lượng các giá trị tương ứng với của các bộ thỏa điều kiện WHERE và khác Null có trong quan hệ.Cú pháp của SQL (p.18)Các Loại Truy Vấn SQLTruy vấn chọn (Select query)Là các truy vấn bắt đầu bằng từ khóa SELECTTrả về 1 giá trị hoặc 1 tập các bộ Truy vấn định nghĩa dữ liệu (Data Definition Query)Là các truy vấn bắt đầu bằng từ khóa CREATE, DELETE, INSERT, ALTER, Sử dụng để tạo,thêm,xóa,sửa các bảng (quan hệ), bộ, ràng buộc,  trong CSDLTruy vấn cập nhật dữ liệu (Data Modification Query)Các loại truy vấn SQLTruy vấn định nghĩa dữ liệu – Tạo lược đồ quan hệVí dụ 1 :CREATE TABLE 	SINHVIEN(MASV Text(10) CONSTRAINT k1 PRIMARY KEY,HOTEN Text(30), NGAYSINH Date, MALOP Text(10),DIEMTB Double ) 	Ghi chú : 	_ Từ in nghiêng là từ khóa của SQL	_ Text, Date, Double,  : các kiểu dữ liệu (của thuộc tính)	_ Text(10) : kiểu dữ liệu Text, có độ dài 10 ký tựTruy vấn định nghĩa dữ liệu (p.1)Tạo lược đồ quan hệ (t.t)_ MASV Text(10) CONSTRAINT k1 PRIMARY KEY :Khai báo thuộc tính MASV là khóa chính với quy tắc ràng buộc tên là k1Ví dụ 2 :CREATE TABLE 	BANGDIEM(MASV Text(10), MAMH Text(10), DIEM Double, CONSTRAINT k2 PRIMARY KEY (MASV, MAMH))Truy vấn định nghĩa dữ liệu (p.2)Thêm,xóa,sửa thuộc tính (cột)Thêm thuộc tính và quan hệVí dụ :ALTER TABLE 	SINHVIEN ADD COLUMN GIOITINH TEXT(10)Sửa kiểu dữ liệu của thuộc tính :ALTER TABLE 	SINHVIEN ALTER COLUMN GIOITINH BOOLEANXóa thuộc tính Ví dụ :ALTER TABLE 	SINHVIEN DROP COLUMN GIOITINHTruy vấn định nghĩa dữ liệu (p.3)Xóa,thêm các ràng buộcXóa ràng buộc khóa chínhVí dụ :ALTER TABLE 	SINHVIEN DROP CONSTRAINT k1Thêm ràng buộc khóa chínhVí dụ :ALTER TABLE 	SINHVIEN ADD CONSTRAINT k1 PRIMARY KEY (MASV)Thêm ràng buộc miền giá trị lên thuộc tínhVí dụ :ALTER TABLE 	SINHVIEN ADD CONSTRAINT k3 CHECK (DIEMTB>=0 AND DIEMTB= 6.5SELECT * FROM SINHVIEN WHERE DIEMTB >= 6.5;Ví dụ 2 : Chọn 10 sinh viên có điểm trung bình cao nhấtSELECT TOP 10 FROM SINHVIEN;Ví dụ 3 : Chọn 10% sinh viên có điểm trung bình cao nhấtSELECT TOP 10% FROM SINHVIEN;Ví dụ 4 : Chọn có loại bỏ các bộ trùng : chọn các mức điểm khác nhau mà các sinh viên đã đạt đượcSELECT DISTINCT DIEMTB FROM SINHVIEN;Lưu ý : Dấu ; cho biết đã kết thúc câu lệnh SQLTruy vấn chọn dữ liệu (p.1)Truy vấn chọn từ nhiều bảngVí dụ 1 : Tìm tất cả các tên học phần mà sinh viên mang mã số SV01 đã đăng ký.SELECT 	HOCPHAN.TENHPFROM 	SINHVIEN,DANGKY_HOCPHAN,HOCPHAN WHERE 	SINHVIEN.MASV = ‘SV01’ AND 	SINHVIEN.MASV = DANGKY_HOCPHAN.MASV 	AND	 DANGKY_HOCPHAN.MAHP = HOCPHAN.MAHP;Lưu ý : 	FROM Q1,Q2,,Qn  	FROM Q1xQ2xxQn	(Tích Descartes)Truy vấn chọn dữ liệu (p.2)Truy vấn chọn có kếtVí dụ 1 : Tìm tất cả các tên học phần mà sinh viên mang mã số SV01 đã đăng ký.SELECT 	HOCPHAN.TENHPFROM 	(SINHVIEN INNER JOIN DANGKY_HOCPHAN ON 	SINHVIEN.MASV = DANGKY_HOCPHAN.MASV)	INNER JOIN HOCPHAN ON 	DANGKY_HOCPHAN.MAHP = HOCPHAN.MAHPWHERE 	MASV = ‘SV01’;	Lưu ý : Phép kết chính là phép chọn có điều kiện từ tích Descartes.Truy vấn chọn dữ liệu (p.3)Truy vấn chọn có sắp thứ tự kết quả trả vềVí dụ 1 : Tìm tất cả các tên sinh viên đã đăng ký học phần có mã là CSDL, sắp thứ tự kết quả trả về theo tên tăng dần, họ tăng dần và mã sinh viên giảm dần.SELECT 	MASV,HO,TENFROM 	(SINHVIEN INNER JOIN DANGKY_HOCPHAN ON 	SINHVIEN.MASV = DANGKY_HOCPHAN.MASVWHERE 	MAHP = ‘CSDL’ORDER BY	TEN ASC, HO ASC, MASV DESC;Lưu ý : Khi thuộc tính giữa các bảng được truy vấn sau từ khóa From không trùng tên thì ta có thể ghi tường minh tên thuộc tính, mà không cần phải ghi : 	.Truy vấn chọn dữ liệu (p.4)Truy vấn chọn có sắp các kết quả trả về theo nhóm (group by)Ví dụ 1 : Tìm tất cả các tên sinh viên đã đăng ký học phần ít nhất 3 học phần trở lên.	SELECT 	SINHVIEN.MASV, SINHVIEN.HOTEN 	FROM 	DANGKY_HOCPHAN INNER JOIN SINHVIEN ON 	DANGKY_HOCPHAN.MASV=SINHVIEN.MASV	GROUP BY 	SINHVIEN.MASV,SINHVIEN.HOTEN	HAVING 	COUNT(DANGKY_HOCPHAN.MAHP)>=3Truy vấn chọn dữ liệu (p.5)Truy vấn chọn lồng nhau (nested/sub query)Là câu lệnh truy vấn khi mà trong biểu thức điều kiện của WHERE hoặc HAVING là một câu truy vấn khác.Ví dụ : Lấy về thông tin của sinh viên có điểm trung bình cao nhất.	SELECT 	MASV,HOTEN	FROM	SINHVIEN	WHERE 	DIEMTB >= 	ALL(SELECT DIEMTB FROM SINHVIEN)Truy vấn chọn dữ liệu (p.6)Các từ khóa trong truy vấn lồng nhauANY, SOME : Kết quả các bộ trả về của query cha so sánh với 1 trong (bất kỳ) các bộ của query con. ALL : Kết quả các bộ trả về của query cha so sánh với tất cả các bộ của query con.IN : Kết quả các bộ trả về của query cha bằng với 1 trong (bất kỳ) các bộ của query con.NOT IN : Kết quả các bộ trả về của query cha không bằng với bất kỳ bộ nào của query con.EXISTS / NOT EXISTS : Kết quả các bộ trả về của query cha được thỏa khi query con có tồn tại ít nhất 1 bộ / không tồn tại bộ nào.Truy vấn chọn dữ liệu (p.7)Truy vấn lồng nhau – Ví dụVí dụ : Lấy về thông tin của các sinh viên có đăng ký môn học CSDL.	SELECT 	MASV,HOTEN	FROM	SINHVIEN	WHERE 	MASV IN (SELECT MASV FROM DANGKY_HOCPHAN WHERE MAHP=‘CSDL’)Truy vấn chọn dữ liệu (p.8)Truy vấn lồng nhau – Ví dụVí dụ : Lấy về thông tin của các sinh viên không có đăng ký môn học CSDL.	SELECT 	MASV,HOTEN	FROM	SINHVIEN	WHERE 	MASV NOT IN (SELECT MASV FROM DANGKY_HOCPHAN WHERE MAHP=‘CSDL’)Truy vấn chọn dữ liệu (p.9)Truy vấn lồng nhau – Ví dụVí dụ : Trả về điểm trung bình cộng của các sinh viên nếu như có ít nhất 1 sinh viên có điểm trung bình >= 5.SELECT 	AVG(DIEMTB) FROM 	SINHVIENWHERE	EXISTS(SELECT DIEMTB FROM SINHVIEN WHERE DIEMTB>=5)Truy vấn chọn dữ liệu (p.10)Truy vấn cập nhật dữ liệu – Cập nhật các bộCú pháp :	UPDATE SET 	 = ,	 = ,		 	 = 	WHERE Truy vấn cập nhật dữ liệu (p.1)Cập nhật các bộ (t.t)Ví dụ : Cộng thêm 1 điểm cho các sinh viên có điểm trung bình >= 4 và =4 AND DIEMTB	WHERE Ví dụ : Xóa các học sinh không có điểm trung bình	DELETE FROM 	SINHVIEN	WHERE	DIEMTB = NullTruy vấn cập nhật dữ liệu (p.3)Thêm các bộ vào quan hệ (bảng)Cú pháp :	INSERT INTO (	,,)	VALUES(, , ) Lưu ý : Các giá trị trong VALUES() phải tương ứng với các thuộc tính trong ()	Nếu có thuộc tính nào trong lược đồ quan hệ không được khai báo trong () và VALUES() thì giá trị của bộ mới được thêm vào ứng với thuộc tính đó sẽ được đặt bằng NullTruy vấn cập nhật dữ liệu (p.4)