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

áp’, 8
 FROM DETAI
 Biên soạn: Nguyễn Thị Mỹ Dung 5
 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
BG_SQL_SERVER 3
Chương 3: Transact-SQL
 2. Cập nhật (Sửa) dữ liệu
 UPDATE 
 SET = ,,
 = ,
 [FROM ]
 [WHERE ]
 Ví dụ 2:1:
 UPDATEUPDATEDE_TAISINHVIEN
 SETSETKINHPHIHOCLUC= KINHPHI = 6.2, NAMSINH+ 2 = 1991
 WHERE MASV = ‘SV006’
 Biên soạn: Nguyễn Thị Mỹ Dung 7
 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
 Biên soạn: Nguyễn Thị Mỹ Dung 8
BG_SQL_SERVER 4
Chương 3: Transact-SQL
 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 ON
 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
BG_SQL_SERVER 5
Chương 3: Transact-SQL
 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 11
 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
 Biên soạn: Nguyễn Thị Mỹ Dung 12
BG_SQL_SERVER 6
Chương 3: Transact-SQL
 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 13
 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'
 Biên soạn: Nguyễn Thị Mỹ Dung 14
BG_SQL_SERVER 7
Chương 3: Transact-SQL
 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
 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.
 Biên soạn: Nguyễn Thị Mỹ Dung 16
BG_SQL_SERVER 8
Chương 3: Transact-SQL
 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:
 SELECT HOTENSV, 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
BG_SQL_SERVER 9
Chương 3: Transact-SQL
 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 Ví dụ: Tìm tất cả họ tên
 hợp
 chủ nhiệm và sinh viên
  Union [ALL]()
 USE QLDETAISV
 SELECT 
 FROM SELECT CHUNHIEM
 [WHERE ] FROM DE_TAI
 UNION UNION
 SELECT SELECT HOTENSV
 FROM 
 FROM SINHVIEN
 [WHERE ]
 Biên soạn: Nguyễn Thị Mỹ Dung 20
BG_SQL_SERVER 10
Chương 3: Transact-SQL
 Intersect [ALL] () Ví dụ: Tìm MASV có
 SELECT quê quán cùng với nơi áp
 dụng đề tài
 FROM 
 USE QLDETAISV
 [WHERE ]
 INTERSECT SELECT MASV, QUEQUAN
 SELECT FROM SINHVIEN
 FROM INTERSECT
 [WHERE <điều_kiện] SELECT MASV, NOIA_D
 FROM SV_DT
 Biên soạn: Nguyễn Thị Mỹ Dung 21
  Except [ALL](-) Ví dụ: Tìm tất cả MASV
 SELECT có quê quán khác với nơi
 áp dụng đề tài
 FROM 
 USE QLDETAISV
 [WHERE ]
 EXCEPT SELECT MASV, QUEQUAN
 SELECT FROM SINHVIEN
 FROM EXCEPT
 [WHERE ] SELECT MASV, NOIA_D
 FROM SV_DT
 Biên soạn: Nguyễn Thị Mỹ Dung 22
BG_SQL_SERVER 11
Chương 3: Transact-SQL
 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 (SELECT s.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
BG_SQL_SERVER 12
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 13
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 14
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 15
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 16
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 17
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 18
Chương 3: Transact-SQL
  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
BG_SQL_SERVER 19
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 20
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 21
Chương 3: Transact-SQL
 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
BG_SQL_SERVER 22