Xem mẫu
- Chương 3: Transact-SQL
GVPT: NGUYỄN THỊ MỸ DUNG
SỐ TC: 2
SỐ TIẾT: LT: 20; TH: 20
Biên soạn: Nguyễn Thị Mỹ Dung 1
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)
Biên soạn: Nguyễn Thị Mỹ Dung 2
BG_SQL_SERVER 1
- Chương 3: Transact-SQL
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
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 ]
Biên soạn: Nguyễn Thị Mỹ Dung 4
BG_SQL_SERVER 2
- Chương 3: Transact-SQL
Ví dụ 1:
INSERT INTO SV_DT (Masv, MaDT, KQ)
VALUES (‘SV001’, ‘DT08’, 8)
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
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:
UPDATE
UPDATE DE_TAI
SINHVIEN
SET
SETKINHPHI
HOCLUC = 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 ]
[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 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
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.
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 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
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
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
chú:: SV thực hiện phép kết Right Join
Ghi chú Join,, Inner
Join,, Cross Join để so sánh kết quả hiển thị bằng SQL
Join
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
- Chương 3: Transact-SQL
Lưu ý 1: Phép giao rs 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
Cách 3:
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 E XIS TS) để 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 k etqua
WHERE k etqua.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
nguon tai.lieu . vn
