Bài giảng Hệ quản trị cơ sở dữ liệu SQL Server - Chương 3: Transact-SQL và truy vấn dữ liệu - Nguyễn Thị Mỹ Dung

Chương 3: Transact-SQL BG_SQL_SERVER 1 GVPT: NGUYỄN THỊ MỸ DUNG SỐ TC: 2 SỐ TIẾT: LT: 20; TH: 20 1Biên soạn: Nguyễn Thị Mỹ Dung Chương 1: Tổng quan về SQL Server (LT: 2) Chương 2: Tạo và quản trị CSDL (LT: 6; TH: 6) Chương 3: Transact-SQL và truy vấn dữ liệu (LT: 6: TH: 8) Chương 4: Lập trình với Transact-SQL (LT: 4: TH: 4) Chương 5: Bảo Mật và Phân Quyền (LT: 2; TH: 2) Chương 6: Kết nối CSDL (Tự học) 2Biên soạn: Nguyễn Thị Mỹ Dung Chương 3: Transact-SQL BG_SQL_SERVER 2 I. Ngôn ngữ xử lý dữ liệu (DML - Data Manipu- lation Language): thêm, sửa, xóa II. Tìm kiếm trên một bảng III. Tìm kiếm trên nhiều bảng IV. Truy vấn con lồng nhau V. Sắp xếp VI. Nhóm dữ liệu và thống kê Bài tập chương 3 Biên soạn: Nguyễn Thị Mỹ Dung 3 Biên soạn: Nguyễn Thị Mỹ Dung 4 1. Thêm dữ liệu vào bảng Dạng 1: Thêm 1 dòng dữ liệu INSERT INTO [(ds_thuộc_tính)] VALUES (, , , ) Dạng 2: Thêm nhiều dòng dữ liệu INSERT INTO [(ds_thuộc_tính)] SELECT , , FROM [WHERE ] Chương 3: Transact-SQL BG_SQL_SERVER 3 Biên soạn: Nguyễn Thị Mỹ Dung 5 Ví dụ 2: Thêm dữ liệu sinh viên SV005 thực hiện tất cả đề tài. INSERT INTO SV_DT SELECT ‘SV005’,MADT, N‘Đồng Tháp’, 8 FROM DETAI Ví dụ 1: INSERT INTO SV_DT (Masv, MaDT, KQ) VALUES (‘SV001’, ‘DT08’, 8) Nhận xét: - Thứ tự các giá trị phải trùng với thứ tự các cột, - Có thể thêm giá trị NULL ở những thuộc tính không là khóa chính hoặc NOT NULL, - Các giá trị thuộc tính khóa không được trùng, - Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV (Khóa chính, tham chiếu, trùng tên,), các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị. Biên soạn: Nguyễn Thị Mỹ Dung 6 Chương 3: Transact-SQL BG_SQL_SERVER 4 Biên soạn: Nguyễn Thị Mỹ Dung 7 2. Cập nhật (Sửa) dữ liệu UPDATE SET = ,, = , [FROM ] [WHERE ] Ví dụ 1: UPDATE DE_TAI SET KINHPHI = KINHPHI + 2 2 UPDATE SINHVIEN SET HOCLUC = 6.2, NAMSINH = 1991 WHERE MASV = ‘SV006’ Biên soạn: Nguyễn Thị Mỹ Dung 8 3. Xóa dữ liệu bảng Dạng 1: Xóa một số bộ từ bảng DELETE FROM -- Nếu đk có trong bảng khác [FROM ] [WHERE ] Dạng 2: Xóa tất cả các bộ từ bảng TRUNCATE TABLE VD1: Xóa những môn học dưới 20 tiết DELETE FROM MON WHERE SOTIET < 20 Chương 3: Transact-SQL BG_SQL_SERVER 5 VD2: Xóa bảng điểm của sinh viên khoa Tin học có điểm bé hơn 2 DELETE FROM KETQUA FROM SINHVIEN S INNER JOIN KHOA K O N S.MAKH = K.MAKH WHERE S.MASV = KETQUA.MASV AND TENKH LIKE N'TIN HỌC' AND DIEM = 4 VD3: Xóa tất cả kết quả của sinh viên TRUNCATE TABLE KETQUA Biên soạn: Nguyễn Thị Mỹ Dung 9 VD4: Xóa tất cả những sinh viên chưa tham gia học tập môn học nào. DELETE FROM SINHVIEN WHERE MASV NOT IN (SELECT MASV FROM SV_DT) Lưu ý: - Câu lệnh Delete sẽ xóa tất cả dữ liệu trên bảng FROM gần nó nhất. - Nếu có nhiều FROM trong câu lệnh Delete thì Bảng tại FROM gần Delete không được đặt bí danh. Biên soạn: Nguyễn Thị Mỹ Dung 10 Chương 3: Transact-SQL BG_SQL_SERVER 6 Biên soạn: Nguyễn Thị Mỹ Dung 11 Nhận xét: - Các lệnh UPDATE, DELETE có thể gây ra vi phạm RBTV (không cho sửa, xóa, hoặc xóa luôn các dòng dữ liệu tham chiếu hoặc dữ liệu tham chiếu sẽ là Null). - Điều kiện trong mệnh đề WHERE sẽ được thực hiện cho các dòng thỏa điều kiện, nếu không có mệnh đề WHERE toàn bộ bảng sẽ có ảnh hưởng. - Tránh vi phạm RBTV khi xóa, nên xóa các dữ liệu bảng nhiều trước, sau đó xóa bảng một. Biên soạn: Nguyễn Thị Mỹ Dung 12 1. Select không có điều kiện SELECT * | FROM Ví dụ 1: In ra thông tin chi tiết của tất cả sviên SELECT * FROM sinhvien Ví dụ 2: In ra mã số, họ tên của tất cả sinh viên SELECT masv, hoten FROM sinhvien Chương 3: Transact-SQL BG_SQL_SERVER 7 Biên soạn: Nguyễn Thị Mỹ Dung 13 2/ Select có điều kiện SELECT * | FROM WHERE [;] [HAVING ] Ví dụ: In ra mã số, họ tên của tất cả sinh viên từ 21 tuổi trở lên. SELECT MASV, HOTEN, NAMSINH FROM SINHVIEN WHERE (YEAR(GETDATE())-NAMSINH)>21 Biên soạn: Nguyễn Thị Mỹ Dung 14 3. Select với Distinct | All - Distinct: loại bỏ phần tử trùng nhau -ALL: lấy tất cả các bộ kể cả phần tử trùng SELECT DISTINCT | All FROM [WHERE ]; VD1: in ra mã đề tài của các sinh viên thực hiện SELECTALL MADT FROM SV_DT VD2: in ra mã đề tài có nơi áp dụng là ‘Dong Thap’ SELECT DISTINCT MADT FROM SV_DT WHERE NOIA_D = 'DONG THAP' Chương 3: Transact-SQL BG_SQL_SERVER 8 Ghi chú: - Mệnh đề WHERE kết hợp với các toán tử: AND, OR: kết hợp nhiều điều kiện  [NOT] LIKE: so sánh chuỗi  BETWEEN AND: so sánh trong khoảng  IS [NOT] NULL: tìm các bộ là (không) rỗng.  [NOT] IN: tìm trong/ ngoài danh sách - Các ký tự so sánh đại diện đối với chuỗi: ‘%’ (nhiều ký tự), ‘_’ (một ký tự). - Khi so sánh các ký tự có dấu, đặt ‘N’ trước chuỗi so sánh: N‘chuỗi’ Biên soạn: Nguyễn Thị Mỹ Dung 15 Biên soạn: Nguyễn Thị Mỹ Dung 16 1. Select nhiều bảng SELECT * | FROM [bídanh1] [bídanh2] ON [WHERE[AND|OR] Trong đó : INNER JOIN: kết trong (thường sử dụng) LEFT [OUTER] JOIN: kết trái RIGHT [OUTER] JOIN: kết phải FULL [OUTER] JOIN: kết đầy đủ CROSS JOIN: kết liên bảng cho phép kết tất cả các bộ có thể có (tương tự phép kết tích đề-các), khi đó không cần bất kỳ điều kiện kết nối nào. Chương 3: Transact-SQL BG_SQL_SERVER 9 Ví dụ 1: Tìm họ tên sinh viên thực hiện tên đề tài, kết quả từ 9 trở lên Cách 1: SELECT HOTENSV, TENDT,KETQUA FROM SINHVIEN S,DETAI D, SV_DT SD WHERE S.MASV = SD.MASV AND SD.MADT= D.MADT AND KETQUA >= 9; Cách 2: SELECTHOTENSV, TENDT,KETQUA FROM ((SINHVIEN S INNER JOIN SV_DT SD ON S.MASV = SD.MASV) INNER JOIN DETAI D ON SD.MADT = D.MADT) WHERE KETQUA >= 9; Biên soạn: Nguyễn Thị Mỹ Dung 17 Ví dụ 2: Tìm họ tên của tất cả sinh viên thực hiện đề tài (có thể có sinh viên không thực hiện đề tài) gồm, họ tên sinh viên, mã đề tài, kết quả SELECT HOTENSV, MADT, KETQUA FROM SINHVIEN S LEFT JOIN SV_DT SD ON S.MASV = SD.MASV Hoặc: SELECT HOTENSV, MADT, KETQUA FROM SINHVIEN S FULL JOIN SV_DT SD ON S.MASV = SD.MASV Ghi chú: SV thực hiện phép kết Right Join, Inner Join, Cross Join để so sánh kết quả hiển thị bằng SQL Biên soạn: Nguyễn Thị Mỹ Dung 18 Chương 3: Transact-SQL BG_SQL_SERVER 10 2. SELECT với INTO INTO tạo ra bảng mới với thuộc tính được chọn từ SELECT. FROM. SELECT ,.. , INTO FROM [WHERE ] VD: Tạo bảng SV với các thông tin MaSV, HotenSV, hocluc từ bảng SINHVIEN và ketqua thực hiện đề tài từ bảng SV_DT SELECT MASV, HOTENSV, HOCLUC, KQ INTO SV FROM SINHVIEN S INNER JOIN SV_DT SD ON S.MASV = SD.MASV Biên soạn: Nguyễn Thị Mỹ Dung 19 3. Các phép toán tập hợp  Union [ALL]() SELECT FROM [WHERE ] UNION SELECT FROM [WHERE ] Ví dụ: Tìm tất cả họ tên chủ nhiệm và sinh viên USE QLDETAISV SELECT CHUNHIEM FROM DE_TAI UNION SELECT HOTENSV FROM SINHVIEN Biên soạn: Nguyễn Thị Mỹ Dung 20 Chương 3: Transact-SQL BG_SQL_SERVER 11  Intersect [ALL] () SELECT FROM [WHERE ] INTERSECT SELECT FROM [WHERE <điều_kiện] Ví dụ: Tìm MASV có quê quán cùng với nơi áp dụng đề tài USE QLDETAISV SELECT MASV, QUEQUAN FROM SINHVIEN INTERSECT SELECT MASV, NOIA_D FROM SV_DT Biên soạn: Nguyễn Thị Mỹ Dung 21  Except [ALL](-) SELECT FROM [WHERE ] EXCEPT SELECT FROM [WHERE ] Ví dụ: Tìm tất cả MASV có quê quán khác với nơi áp dụng đề tài USE QLDETAISV SELECT MASV, QUEQUAN FROM SINHVIEN EXCEPT SELECT MASV, NOIA_D FROM SV_DT Biên soạn: Nguyễn Thị Mỹ Dung 22 Chương 3: Transact-SQL BG_SQL_SERVER 12 Lưu ý 1: Phép giao rs có thể viết theo nhiều cách như sau: Cách 1: SELECT * FROM r INTERSECT SELECT * FROM s Cách 2: SELECT * FROM r WHERE r.c IN (SELECT s.c FROM s) Cách 3: SELECT * FROM r WHERE EXISTS (SELECT * FROM s WHERE s.c=r.c) Biên soạn: Nguyễn Thị Mỹ Dung 23 Lưu ý 2: Phép trừ r-s có thể viết theo nhiều cách như sau: Cách 1: SELECT* FROM r EXCEPT SELECT* FROM s Cách 2: SELECT* FROM r WHERE r.c NOT IN (SELECTs.c FROM s) Cách3: SELECT* FROM r WHERE NOT EXISTS (SELECT* FROM s WHERE s.c=r.c) Biên soạn: Nguyễn Thị Mỹ Dung 24 Chương 3: Transact-SQL BG_SQL_SERVER 13 Truy vấn con là một câu lệnh SELECT được lồng vào bên trong một câu lệnh SELECT, INSERT, UPDATE, DELETE hoặc bên trong một truy vấn khác. Cú pháp: SELECT [ALL | DISTINCT] FROM WHERE AND | OR (SELECT FROM [WHERE ]) Biên soạn: Nguyễn Thị Mỹ Dung 25 1. Sử dụng truy vấn con với toán tử IN Khi cần thực hiện phép kiểm tra giá trị của một biểu thức có xuất hiện (không xuất hiện) trong tập các giá trị của truy vấn con, ta có thể sử dụng toán tử IN (NOT IN) như sau: WHERE [NOT] IN () Ví dụ: Tìm những sinh viên không thực hiện đề tài SELECT HOTENSV, HOCLUC FROM SINHVIEN WHERE MASV NOT IN (SELECT MASV FROM SV_DT) Biên soạn: Nguyễn Thị Mỹ Dung 26 Chương 3: Transact-SQL BG_SQL_SERVER 14 2. Truy vấn con với EXISTS Lượng từ EXISTS (NOT EXISTS) để kiểm tra xem một truy vấn con có trả về dòng kết quả nào hay không được sử dụng trong truy vấn con dưới dạng: WHERE [NOT] EXISTS () Ví dụ: cho biết họ tên của những sinh viên hiện chưa có điểm thi của bất kỳ một môn học nào SELECT hosv,tensv FROM sinhvien WHERE NOT EXISTS (SELECT masv FROM ketqua WHERE ketqua.masv=sinhvien.masv) Biên soạn: Nguyễn Thị Mỹ Dung 27 3. Truy vấn con với mệnh đề HAVING Một truy vấn con có thể được sử dụng trong mệnh đề HAVING của một truy vấn khác. Kết quả của truy vấn con được sử dụng để tạo điều kiện đối với các hàm gộp. Ví dụ: Cho biết mã, tên và trung bình điểm thi của các môn học có trung bình lớn hơn trung bình điểm của tất cả các môn học. SELECT KETQUA.MAMH,TENMH, AVG(DIEM) FROM KETQUA,MONHOC WHERE KETQUA.MAMH = MON.MAMH GROUP BY KETQUA.MAMH,TENMH HAVING AVG(DIEM) > (SELECT AVG(DIEM) FROM KETQUA) Biên soạn: Nguyễn Thị Mỹ Dung 28 Chương 3: Transact-SQL BG_SQL_SERVER 15 4. Thực hiện phép chia với truy vấn lồng nhau Sử dụng toán tử NOT EXISTS để thực hiện: Cú pháp: SELECT * FROM R WHERE NOT EXISTS (SELECT * FROM S WHERE NOT EXISTS (SELECT * FROM R_S WHERE R_S.C1 = S.C1 AND R_S.C2 = R.C2)) Biên soạn: Nguyễn Thị Mỹ Dung 29 Ví dụ: Tìm thông tin sinh viên thực hiện tất cả các đề tài SELECT * FROM SINHVIEN WHERE NOT EXISTS (SELECT * FROM DETAI WHERE NOT EXISTS (SELECT * FROM SV_DT WHERE SV_DT.MADT = DETAI.MADT AND SV_DT.MASV = SINHVIEN.MASV)) Biên soạn: Nguyễn Thị Mỹ Dung 30 Chương 3: Transact-SQL BG_SQL_SERVER 16 Cho phép sắp xếp các dòng trong kết quả câu truy vấn theo thứ tự tăng dần (hoặc giảm dần) dựa trên một hoặc nhiều trường làm tiêu chí Cú pháp: SELECT FROM [WHERE ] [GROUP BY ] ORDER BY ASC|DESC  ASC: giá trị mặc nhiên, sắp xếp kết quả theo thứ tự tăng dần.  DESC: sắp xếp kết quả theo thứ tự giảm dần Biên soạn: Nguyễn Thị Mỹ Dung 31 Ví dụ: In ra thông tin gồm mã số, họ tên và kết quả tương ứng của sinh viên thực hiện các đề tài có nơi áp dụng ở Đồng Tháp. Danh sách được sắp thứ tự giảm dần theo kết quả thực hiện. SELECT Sinhvien.MaSV, Hoten, KQ FROM Sinhvien INNER JOIN SV_DT ON Sinhvien.MaSV = SV_DT.MaSV WHERE NoiA_D = N‘Đồng Tháp’ ORDER BY KQ DESC Biên soạn: Nguyễn Thị Mỹ Dung 32 Chương 3: Transact-SQL BG_SQL_SERVER 17 Sử dụng ORDER BY với TOP Mệnh đề TOP dùng để hạn chế số bộ trong truy vấn. Cú pháp: SELECT TOP FROM WHERE ORDER BY ASC|DESC Ví dụ: Tìm những đề tài có kinh phí cao nhất và nhì. SELECT TOP 2 MADT FROM SV_DT ORDER BY kinhphi DESC Biên soạn: Nguyễn Thị Mỹ Dung 33 Sử dụng ORDER BY với hàm RANK() Hàm RANK() dùng để xếp hạng theo ORDER BY. Cú pháp: SELECT RANK() OVER (ORDER BY [ASC | DESC]) AS , FROM [WHERE ] Ví dụ: Xếp hạng sinh viên theo kết quả thực hiện đề tài từ cao đến thấp. SELECT RANK() OVER (ORDER BY KQ DESC) AS XEPHANG, HOTENSV, KQ FROM SINHVIEN S INNER JOIN SV_DT SD ON S.MASV = SD.MASV Biên soạn: Nguyễn Thị Mỹ Dung 34 Chương 3: Transact-SQL BG_SQL_SERVER 18 Sử dụng ORDER BY với hàm ROW_NUMBER() Hàm ROW_NUMBER() dùng để đánh số thứ tự dòng theo ORDER BY. Cú pháp: SELECT ROW_NUMBER() OVER (ORDER BY (SELECT )) AS , FROM [WHERE ] Ví dụ: Thêm cột số thứ tự cho truy vấn. SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, HOTENSV, KQ FROM SINHVIEN S INNER JOIN SV_DT SD ON S.MASV = SD.MASV Biên soạn: Nguyễn Thị Mỹ Dung 35 1. Thống kê với các hàm kết tập Cú pháp: SELECT [,] FROM [WHERE ] [GROUP BY ] [HAVING ]  Các hàm kết tập bao gồm: SUM, MAX, MIN, AVG và COUNT.  Đặt lại tên trường: Trong một số trường hợp tên trường của kết quả truy vấn không phù hợp ta cần đặt lại tên trường Cú pháp: AS Biên soạn: Nguyễn Thị Mỹ Dung 36 Chương 3: Transact-SQL BG_SQL_SERVER 19  Cách sử dụng các hàm: a. Hàm SUM (): Trả về tổng giá trị của tất cả các dòng của một trường kiểu số Ví dụ: Tính tổng kinh phí của tất cả các đề tài SELECT SUM (Kinhphi) as Tong_KP FROM Detai; b. Hàm MAX (): Trả về giá trị lớn nhất trong tất cả các dòng của một trường Ví dụ: Tìm kinh phí lớn nhất của tất cả các đề tài SELECT MAX(Kinhphi) AS KP_MAX FROM Detai Biên soạn: Nguyễn Thị Mỹ Dung 37 c. Hàm MIN (): Trả về giá trị nhỏ nhất trong tất cả các dòng của một trường Ví dụ: Tìm kinh phí nhỏ nhất của tất cả các đề tài SELECT MIN(Kinhphi) AS KP_MIN FROM Detai d. Hàm AVG (): Trả về giá trị trung bình cộng của tất cả các dòng một trường kiểu số Ví dụ: Tính trung bình cộng kinh phí của tất cả các đề tài SELECT AVG(Kinhphi) AS TBC_KP FROM Detai Biên soạn: Nguyễn Thị Mỹ Dung 38 Chương 3: Transact-SQL BG_SQL_SERVER 20 e. Hàm COUNT (): Đếm các dòng trong bảng theo một hoặc một số trường nào đó Ví dụ 1: In ra số lượng đề tài mà thầy Lê Đức Phúc làm chủ nhiệm SELECT COUNT(MaDT) AS So_DT FROM Detai WHERE Chunhiem = ‘Lê Đức Phúc’ Ví dụ 2: In ra họ tên của các giáo viên chủ nhiệm từ 2 đề tài trở lên SELECT Chunhiem, COUNT(MaDT) AS SoLG FROM Detai GROUP BY Chunhiem HAVING COUNT(MaDT) >= 2 Biên soạn: Nguyễn Thị Mỹ Dung 39 2. Gom nhóm sử dụng Compute, Compute By Các mệnh đề Compute và Compute By sinh ra các dòng chi tiết và một dòng tổng chính. Compute By sử dụng giống Group By. Thứ tự trong Compute By giống thứ tự trong Order By Cú pháp: SELECT , FROM WHERE [ORDER BY ] [COMPUTE ] [BY ] Biên soạn: Nguyễn Thị Mỹ Dung 40 Chương 3: Transact-SQL BG_SQL_SERVER 21 VD1: Liệt kê bảng điểm chi tiết của sinh viên và cho biết điểm trung bình của cả lớp SELECT S.MASV, (HOSV+' '+TENSV) AS HTSV, DIEM FROM SINHVIEN S INNER JOIN KETQUA K ON S.MASV = K.MASV COMPUTE AVG(DIEM) VD 2: Liệt kê bảng điểm chi tiết của sinh viên và cho biết điểm trung bình của từng sinh viên SELECT S.MASV, (HOSV+' '+TENSV) AS HTSV, DIEM FROM SINHVIEN S INNER JOIN KETQUA K ON S.MASV = K.MASV ORDER BY S.MASV COMPUTE AVG(DIEM) BY S.MASV Biên soạn: Nguyễn Thị Mỹ Dung 41 3. Gom nhóm tổng hợp với Rollup, Cube Để thêm các dòng thống kê từng nhóm, thêm toán tử Rollup hoặc Cube với mệnh đề Group by Cú pháp: SELECT FROM [WHERE ] [GROUP BY ] [WITH ROLLUP | CUBE] Biên soạn: Nguyễn Thị Mỹ Dung 42 Chương 3: Transact-SQL BG_SQL_SERVER 22 VD 1: Liệt kê bảng điểm chi tiết của sinh viên và cho biết điểm trung bình của từng sinh viên SELECT S.MASV, (HOSV+' '+TENSV) AS HOTENSV, MAMH, AVG(DIEM) AS ĐTB FROM SINHVIEN S INNER JOIN KETQUA K ON S.MASV = K.MASV GROUP BY S.MASV, HOSV, TENSV, MAMH WITH ROLLUP VD2: SV thực hiện yêu cầu của VD1 với câu lệnh với CUBE để so sánh kết quả thực hiện. Biên soạn: Nguyễn Thị Mỹ Dung 43 - Ngôn ngữ xử lý dữ liệu (Insert, Update, Delete) - Truy vấn dữ liệu trên một và nhiều bảng (select from where) - Truy vấn dữ liệu tập hợp (UNION, INTERSECT, EXCEPT), truy vấn lồng nhau - Truy vấn sử dụng hàm kết tập (SUM, MAX, MIN, AVG, COUNT) - Truy vấn sắp xếp dữ liệu (ORDER BY, TOP, RANK, ROW_NUMBER,) - Truy vấn thống kê trên nhóm (GROUP BY, ROLLUP, CUBE, COMPUTE, COMPUTE BY) Biên soạn: Nguyễn Thị Mỹ Dung 44