Bài giảng Cơ sở dữ liệu - Chương 5: SQL - Nguyễn Thị Như Anh

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 5: SQL - Nguyễn Thị Như Anh

1. Mệnh đề SELECT Lấy ra tất cả các cột Lấy tất cả các cột của quan hệ SELECT * kết quả FROM KHOA WHERE PHONG='I53' AND NAMTL = '1995' MaKhoa TenKhoa Phong NamTL DienThoai TruongKhoa NgayNhanChuc CNTT Công nghệ thông tin I53 1995 08313964145 GV130 01/01/2007 SH Sinh học B32 1975 08313123545 GV250 01/01/1990 PHG='I53'NamTL='1995' (KHOA) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 28 Mệnh đề SELECT (tt) Lấy ra một số cột SELECT MAKHOA, TENKHOA, PHONG FROM KHOA WHERE PHONG='I53' AND NAMTL = '1995' MaKhoa TenKhoa Phong CNTT Công nghệ thông tin I53 MAKHOA, TENKHOA, PHONG(PHG='I53'NamTL='1995' (KHOA)) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 29 14
2. Mệnh đề SELECT (tt) Mở rộng SELECT MAGV, LUONG*1.1 AS 'LUONG10%' FROM GIAOVIEN WHERE PHAI='Nam' MAGV LUONG10% GV001 550000 MAGV,LUONG10%( MAGV,LUONG*1.1(PHAI=‘Nam’(GIAOVIEN))) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 32 Mệnh đề SELECT (tt) Loại bỏ các dòng trùng nhau SELECT LUONG SELECT DISTINCT LUONG FROM GIAOVIEN FROM GIAOVIEN WHERE PHAI=‘Nam’ WHERE PHAI=‘Nam’ LUONG LUONG 30000 30000 25000 25000 25000 38000 38000 - Tốn chi phí - Người dùng muốn thấy © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 33 16
3. Mệnh đề WHERE (tt) Độ ưu tiên SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE (TENBM = 'Hệ thống thông tin' OR TENBM = 'Mạng máy tính') AND GIAOVIEN.MABM = BOMON.MABM Độ ưu tiên mặc định của các phép toán: từ trái qua phải. Nên sử dụng dấu ngoặc thể hiện tường minh sự ưu tiên của các phép toán. © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 36 Mệnh đề WHERE (tt) BETWEEN SELECT MAGV, HOTEN FROM GIAOVIEN WHERE LUONG >= 20000 AND LUONG <= 30000 SELECT MAGV, HOTEN FROM GIAOVIEN WHERE LUONG BETWEEN 20000 AND 30000 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 37 18
4. Mệnh đề WHERE (tt) NOT LIKE SELECT MAGV, HOTEN FROM GIAOVIEN WHERE HOTEN LIKE ‘Nguyen’ phủ định SELECT MAGV, HOTEN FROM GIAOVIEN WHERE HOTEN NOT LIKE ‘Nguyen’ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 40 Mệnh đề WHERE (tt) Ngày giờ SELECT MAGV, HOTEN FROM GIAOVIEN WHERE NGAYSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’ ‘1955-12-08’ YYYY-MM-DD ’17:30:00’ HH:MI:SS ’12/08/1955’MM/DD/YYYY ’05:30 PM’ ‘December 8, 1955’ ‘1955-12-08 17:30:00’ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 43 20
5. Mệnh đề FROM Mệnh đề FROM tương đương với phép x trong đại số quan hệ SELECT * GIAOVIEN x BOMON FROM GIAOVIEN, BOMON SELECT * FROM GIAOVIEN, BOMON MAGV MAB MABM TENBM WHERE TRUE M 001 HTTT Hệ thống thông tin HTTT Sử dụng thêm điều 001 MTT Mạng máy tính HTTT kiện ở WHERE để biểu 001 CNPM Công nghệ PM HTTT diễn phép kết 002 MMT HTTT Hệ thống thông tin 002 MMT MTT Mạng máy tính GIAOVIEN BOMON C 002 MMT CNPM Công nghệ PM SELECT * FROM GIAOVIEN, BOMON WHERE C © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 46 Mệnh đề FROM (tt) Tên bí danh SELECT MAGV, MABM, TENBM Nhập nhằng (Ambigous) ??? FROM GIAOVIEN, BOMON WHERE MABM = MABM MAGV MAB MABM TENBM M 001 HTTT HTTT Hệ thống thông tin SELECT G.MAGV, G.MABM, B.TENBM 001 HTTT MTT Mạng máy tính FROM GIAOVIEN G, BOMON AS B 001 HTTT CNPM Công nghệ PM 002 MMT HTTT Hệ thống thông tin WHERE G.MABM = B.MABM 002 MMT MTT Mạng máy tính 002 CNPM Công nghệ PM MMT Đặt bí danh cho bảng sử dụng khoảng trắng hoặc từ khoá AS MAGV MABM TENBM 001 HTTT Hệ thống thông tin Điều kiện kết để tìm ra bộ môn của giáo viên 002 MTT Mạng máy tính © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 47 22
6. Ví dụ 3 ▪ Tìm họ tên của giáo viên viên thuộc bộ môn “HTTT” có tham gia vào đề tài “ Mobile Database” với số tiền phụ cấp cho mỗi công việc trên 10 triệu. SELECT GV.HOTEN FROM GIAOVIEN GV, THAMGIADT TG, DETAI DT WHERE GV.MAGV = TG.MAGV AND TG.MADT = DT.MADT AND GV.MABM=‘HTTT’ AND DT.TENDT=‘Mobile Database’ AND TG.PHUCAP>10 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 50 Ví dụ 4 ▪ Tìm họ tên của từng giáo viên và người phụ trách chuyên môn trực tiếp của nhân viên đó. © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 51 24
7. Mệnh đề ORDER BY (tt) ▪ Ví dụ SELECT * FROM THAMGIADT ORDER BY MAGV DESC, MADT, STT DESC MAGV MADT STT MAGV MADT STT GV01 DT01 1 GV02 DT01 3 GV01 DT01 2 GV02 DT01 2 GV01 DT02 1 GV02 DT03 4 GV02 DT01 2 GV02 DT03 1 GV02 DT01 3 GV01 DT01 2 GV02 DT03 1 GV01 DT01 1 GV02 DT03 4 GV01 DT02 1 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 54 Nội dung chi tiết ▪ Giới thiệu ▪ Định nghĩa dữ liệu ▪ Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số dạng truy vấn khác ▪ Cập nhật dữ liệu ▪ Khung nhìn (view) ▪ Chỉ mục (index) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 55 26
8. Ví dụ 6 ▪ Cho biết mã của các giáo viên có họ tên bắt đầu là 'Nguyễn' và lương trên 200000 hoặc, giáo viên là trưởng bộ môn nhận chức sau năm 1995 SELECT MAGV FROM GIAOVIEN WHERE HOTEN LIKE N'Nguyễn%' AND LUONG > 200000 UNION SELECT TRUONGBM FROM BOMON WHERE YEAR(NGAYNHANCHUC)>=1995 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 58 Ví dụ 7 ▪ Tìm những giáo viên vừa là trưởng bộ môn vừa chủ nhiệm đề tài SELECT TRUONGBM FROM BOMON INTERSECT SELECT GVCNDT FROM DETAI SELECT BM.TRUONGBM FROM BOMON BM, DETAI DT WHERE BM.TRUONGBM = DT.GVCNDT © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 59 28
9. Truy vấn lồng (tt) ▪ Các câu lệnh SELECT có thể lồng nhau ở nhiều mức ▪ Câu truy vấn con thường trả về một tập các giá trị ▪ Các câu truy vấn con trong cùng một mệnh đề WHERE được kết hợp bằng phép nối logic ▪ Mệnh đề WHERE của câu truy vấn cha - - So sánh tập hợp thường đi cùng với một số toán tử ⚫ IN, NOT IN ⚫ ALL ⚫ ANY hoặc SOME - Kiểm tra sự tồn tại ⚫ EXISTS ⚫ NOT EXISTS © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 62 Truy vấn lồng (tt) ▪ Có 2 loại truy vấn lồng - Lồng phân cấp ⚫ Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha ⚫ Khi thực hiện, câu truy vấn con sẽ được thực hiện trước - Lồng tương quan ⚫ Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha ⚫ Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 63 30
10. Ví dụ 10 ▪ Tìm những giáo viên không tham gia đề tài nào SELECT * FROM GIAOVIEN WHERE MAGV NOT IN(SELECT MAGV FROM THAMGIADT) SELECT * FROM GIAOVIEN WHERE MAGV ANY (SELECT GV.LUONG FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND BM.TENBM = N'Công nghệ phần mềm') SELECT GV1.* FROM GIAOVIEN GV1, GIAOVIEN GV2, BOMON BM WHERE GV2.MABM = BM.MABM AND BM.TENBM = N'Công nghệ phần mềm' AND GV1.LUONG > GV2.LUONG © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 67 32
11. Ví dụ - Lồng tương quan ▪ Tìm những giáo viên là trưởng bộ môn SELECT MAGV, HOTEN FROM GIAOVIEN Sử dụng phép truy vấn lồng với IN WHERE MAGV IN (SELECT TRUONGBM FROM BOMON) SELECT MAGV, HOTEN FROM GIAOVIEN GV WHERE EXISTS (SELECT * Sử dụng truy vấn lồng với EXISTS FROM BOMON BM WHERE BM.TRUONGBM = GV.MAGV) Giáo viên là trưởng bộ môn khi tồn tại một bộ môn có TRUONGBM = MAGV của giáo viên đó Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 70 Ví dụ - Lồng tương quan ▪ Tìm những giáo viên có lương lớn nhất Giáo viên là có lương lớn SELECT MAGV, HOTEN nhất khi không tồn tại một FROM GIAOVIEN GV giáo viên nào mà có lương WHERE NOT EXISTS (SELECT * lớn hơn giáo viên đó FROM GIAOVIEN GV2 WHERE GV2.LUONG > GV.LUONG) SELECT MAGV, HOTEN Giáo viên là có lương lớn nhất khi lương của giáo viên FROM GIAOVIEN GV lớn hơn hoặc bằng tất cả WHERE LUONG >= ALL (SELECT LUONG lương của các giáo viên (lồng FROM GIAOVIEN GV2) phân cấp) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 71 34
12. Ví dụ 16 ▪ Tìm những giáo viên có lương lớn hơn lương của ít nhất một giáo viên bộ môn ‘Công nghệ phần mềm’ SELECT * FROM GIAOVIEN GV1 WHERE EXISTS (SELECT * FROM GIAOVIEN GV2, BOMON BM WHERE GV2.MABM = BM.MABM AND BM.TENBM = N'Công nghệ phần mềm' AND GV1.LUONG > GV2.LUONG) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 74 Ví dụ 17 ▪ Tìm những trưởng bộ môn tham gia tối thiểu 1 đề tài SELECT * FROM GIAOVIEN GV WHERE EXISTS (SELECT * FROM BOMON BM WHERE GV.MAGV =BM.TRUONGBM) AND EXISTS (SELECT * FROM THAMGIADT PC WHERE PC.MAGV = GV.MAGV) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 75 36
13. Ví dụ 18 ▪ Tìm những giáo viên có tham gia đề tài SELECT * FROM GIAOVIEN GV WHERE EXISTS (SELECT * FROM THAMGIADT PC WHERE PC.MAGV = GV.MAGV) Nếu giáo viên GV có tham gia đề tài → câu truy vấn bên trong sẽ có dữ liệu (>= 1 dòng) → mệnh EXISTS (S F W) có giá trị TRUE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 78 Ví dụ 19 ▪ Tìm những giáo viên không tham gia đề tài SELECT * FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT * FROM THAMGIADT PC WHERE PC.MAGV = GV.MAGV) Nếu giáo viên GV không tham gia đề tài → câu truy vấn bên trong sẽ rỗng (0 dòng) → NOT EXISTS (S F W) có giá trị TRUE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 79 38
14. Phép chia trong SQL (tt) ▪ Sử dụng NOT EXISTS để biểu diễn SELECT R1.A, R1.B, R1.C FROM R R1 WHERE NOT EXISTS ( SELECT * FROM S WHERE NOT EXISTS ( SELECT * FROM R R2 WHERE R2.D=S.D AND R2.E=S.E AND R1.A=R2.A AND R1.B=R2.B AND R1.C=R2.C )) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 82 Ví dụ 20 Tìm tên các giáo viên được phân công làm tất cả các đề tài - Tìm tên các giáo viên mà không có đề tài nào là không được phân công làm - Tập bị chia: THAMGIADT(MAGV, MADT) - Tập chia: DETAI(MADT) - Tập kết quả: KQ(MAGV) - Kết KQ với GIAOVIEN để lấy ra TENGV © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 83 40
15. Ví dụ 20 – Not Exists SELECT DISTINCT GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, THAMGIADT PC1 WHERE GV.MAGV = PC1.MAGV AND 0 = (SELECT COUNT(*) FROM DETAI DT WHERE NOT EXISTS (SELECT * FROM THAMGIADT PC2 WHERE PC2.MAGV = PC1.MAGV AND DT.MADT = PC2.MADT)) Tìm những giáo viên mà số lượng đề tài chưa tham gia bằng 0 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 86 Phép chia – với Count ▪ Cho R(A,B), S(B), thực hiện RS SELECT R.A FROM R [WHERE R.B IN (SELECT S.B FROM S [WHERE ]] GROUP BY R.A HAVING COUNT(DISTINCT R.B) = ( SELECT COUNT(S.B) FROM S [WHERE ]) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 87 42
16. Bài tập phép chia 1. Cho biết mã số, họ tên, ngày sinh của giáo viên tham gia tất cả các công việc của đề tài ‘Ứng dụng hóa học xanh’. 2. Cho biết mã số, họ tên, tên bộ môn và tên người quản lý chuyên môn của giáo viên tham gia tất cả các đề tài thuộc chủ đề ‘Nghiên cứu phát triển’. 3. Cho biết họ tên, ngày sinh, tên khoa, tên trưởng khoa của giáo viên tham gia tất cả các đề tài có giáo viên ‘Nguyễn Hoài An’ tham gia. 4. Cho biết họ tên giáo viên khoa ‘Công nghệ thông tin’ tham gia tất cả các công việc của đề tài có trưởng bộ môn của bộ © Bộ môn HTTT đông - Khoa CNTTnhất - Trường khoa ĐH ‘Công KHTN nghệ thông tin’ làm chủ nhiệm. 90 Nội dung chi tiết ▪ Giới thiệu ▪ Định nghĩa dữ liệu ▪ Truy vấn dữ liệu - Truy vấn cơ bản - Tập hợp, so sánh tập hợp và truy vấn lồng - Hàm kết hợp và gom nhóm - Một số dạng truy vấn khác ▪ Cập nhật dữ liệu ▪ Khung nhìn (view) ▪ Chỉ mục (index) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 91 44
17. Ví dụ 23 ▪ Cho biết số lượng giáo viên của bộ môn ‘Mạng máy tính’ SELECT COUNT(*) AS SL_GV FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND TENBM=N'Mạng máy tính' © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 94 Ví dụ 25 ▪ Cho biết số lượng giáo viên của từng bộ môn Bộ môn Số lượng HTTT 2 CNPM 1 MMT 1 MANV HOTEN MABM GV001 Nguyễn Văn A HTTT GV002 Trần Văn B HTTT GV003 Trần Thị C CNPM GV004 Đặng Thị D MMT © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 96 46
18. Ví dụ 27 ▪ Với mỗi giáo viên cho biết mã số, mã đề tài và số công việc mà họ tham gia ứng với mỗi đề tài MAGV MADT STT GV001 DT001 1 GV001 DT001 2 GV001 DT002 1 GV002 DT002 2 GV003 DT001 3 GV003 DT002 3 SELECT PC.MAGV, PC.MADT, COUNT(*) AS 'Số lượng công việc' FROM THAMGIADT PC GROUP BY PC.MAGV, PC.MADT © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 99 Ví dụ 28 ▪ Cho biết những giáo viên tham gia từ 2 công việc trở lên cho mỗi đề tài? MAGV MADT STT GV001 DT001 1 GV001 DT001 2 GV001 DT002 1 GV002 DT002 2 GV003 DT001 3 GV003 DT002 3 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 100 48
19. Ví dụ 30 ▪ Cho biết những giáo viên tham gia từ 2 đề tài trở lên MAGV MADT STT GV001 DT001 1 GV001 DT001 2 GV001 DT002 1 GV002 DT002 2 GV003 DT001 3 GV003 DT002 3 SELECT PC.MAGV, COUNT(DISTINCT MADT) AS 'Số lượng đề tài' FROM THAMGIADT PC GROUP BY PC.MAGV HAVING COUNT(DISTINCT MADT) >= 2 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 103 Ví dụ 31 ▪ Cho biết những bộ môn (TENBM) có lương trung bình của các giáo viên lớn hơn 20000 SELECT GV.MABM, AVG(GV.LUONG) AS 'Lương trung bình' FROM GIAOVIEN GV GROUP BY GV.MABM HAVING AVG(GV.LUONG)>20000 SELECT BM.TENBM, AVG(GV.LUONG) AS 'Lương trung bình' FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM GROUP BY BM.MABM, BM.TENBM HAVING AVG(GV.LUONG)>=20000 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 104 50
20. Ví dụ 32 ▪ Tìm những phòng ban có lương trung bình cao nhất SELECT GV.MABM, AVG(GV.LUONG) AS 'Lương trung bình' FROM GIAOVIEN GV GROUP BY GV.MABM HAVING AVG(GV.LUONG)) = (SELECT MAX(AVG(GV.LUONG)) FROM GIAOVIEN GV GROUP BY GV.MABM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 107 Ví dụ 33 ▪ Tìm những phòng ban có lương trung bình cao nhất SELECT GV.MABM, AVG(GV.LUONG) AS 'Lương trung bình' FROM GIAOVIEN GV GROUP BY GV.MABM HAVING AVG(GV.LUONG)>= ALL(SELECT AVG(GV.LUONG) FROM GIAOVIEN GV GROUP BY GV.MABM) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 108 52
21. Một số dạng truy vấn khác ▪ Truy vấn con ở mệnh đề FROM ▪ Điều kiện kết ở mệnh đề FROM - Phép kết tự nhiên - Phép kết ngoàI ▪ Cấu trúc CASE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 111 Truy vấn con ở mệnh đề FROM ▪ Kết quả trả về của một câu truy vấn phụ là một bảng - Bảng trung gian trong quá trình truy vấn - Không có lưu trữ thật sự ▪ Cú pháp SELECT FROM R1, R2, ( ) AS tên_bảng WHERE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 112 54
22. Điều kiện kết ở mệnh đề FROM ▪ Kết bằng SELECT FROM R1 [INNER] JOIN R2 ON WHERE ▪ Kết ngoài SELECT FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON WHERE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 115 Ví dụ 36 ▪ Tìm mã và tên các giáo viên làm việc tại bộ môn ‘Hệ thống thông tin’ SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE TENBM = N'Hệ thống thông tin' AND GIAOVIEN.MABM = BOMON.MABM SELECT MAGV, HOTEN FROM GIAOVIEN GV INNER JOIN BOMON BM ON GV.MABM = BM.MABM WHERE TENBM = N'Hệ thống thông tin' © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 116 56
23. Ví dụ 38 ▪ Cho biết họ tên các giáo viên đã đến tuổi về hưu (nam 60 tuổi, nữ 55 tuổi) SELECT HOTEN FROM GIAOVIEN WHERE YEAR(GETDATE()) - YEAR(NGAYSINH) >= ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 119 Ví dụ 39 ▪ Cho biết họ tên các giáo viên và năm về hưu SELECT GV.HOTEN, YEAR(GV.NGAYSINH) + ( CASE PHAI WHEN 'Nam' THEN 60 WHEN 'Nu' THEN 55 END) AS NAMVEHUU FROM GIAOVIEN GV SELECT GV.HOTEN, ( CASE PHAI WHEN 'Nam' THEN YEAR(NGAYSINH) + 60 WHEN 'Nu' THEN YEAR(NGAYSINH) + 55 END) AS NAMVEHUU FROM GIAOVIEN GV © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 120 58
24. Lệnh INSERT ▪ Dùng để thêm 1 hay nhiều dòng vào bảng ▪ Để thêm dữ liệu - Tên quan hệ - Danh sách các thuộc tính cần thêm dữ liệu - Danh sách các giá trị tương ứng © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 123 Lệnh INSERT (tt) ▪ Cú pháp (thêm 1 dòng) INSERT INTO ( ) VALUES ( ) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 124 60
25. Lệnh INSERT (tt) ▪ Cú pháp (thêm nhiều dòng) INSERT INTO ( ) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 127 Ví dụ 41 CREATE TABLE THONGKE_BM ( TENBM NVARCHAR(50), SL_GV INT, LUONG_TC INT PRIMARY KEY(TENBM) ) INSERT INTO THONGKE_BM SELECT BM.TENBM, COUNT(GV.MAGV), SUM(GV.LUONG) FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM GROUP BY BM.MABM, BM.TENBM © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 128 62
26. Ví dụ 43 ▪ Xóa đi những giáo viên ở bộ môn ‘Hệ thống thông tin’ DELETE FROM GIAOVIEN WHERE MABM IN (SELECT MABM FROM BOMON WHERE TENBM = N'Hệ thống thông tin') DELETE FROM GIAOVIEN FROM BOMON BM WHERE GIAOVIEN.MABM = BM.MABM AND BM.TENBM = N'Hệ thống thông tin' © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 131 Lệnh DELETE (tt) ▪ Nhận xét - Số lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERE - Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị xóa - Lệnh DELETE có thể gây ra vi phạm RB tham chiếu ⚫ Không cho xóa ⚫ Xóa luôn những dòng có giá trị đang tham chiếu đến – CASCADE ⚫ Đặt NULL cho những giá trị tham chiếu © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 132 64
27. Lệnh UPDATE ▪ Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảng ▪ Cú pháp UPDATE SET = , = , [WHERE ] © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 135 Ví dụ 44 UPDATE GIAOVIEN SET NGAYSINH='08/12/1965' WHERE MAGV='GV001' UPDATE GIAOVIEN SET LUONG=LUONG*1.1 © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 136 66
28. Ví dụ 47 ▪ Tăng lương 10% cho tất cả các giáo viên làm cho đề tài ‘DT001’ nhiều hơn 3 công việc. UPDATE GIAOVIEN SET LUONG = LUONG * 1.1 WHERE MAGV IN (SELECT PC.MAGV FROM THAMGIADT TG WHERE TG.MADT = 'DT001' GROUP BY TG.MAGV, TG.MADT HAVING COUNT(*) >= 3) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 139 Lệnh UPDATE ▪ Nhận xét - Những dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mới - Nếu không chỉ định điều kiện ở mệnh đề WHERE, tất cả các dòng trong bảng sẽ bị cập nhật - Lệnh UPDATE có thể gây ra vi phạm RB tham chiếu ⚫ Không cho sửa ⚫ Sửa luôn những dòng có giá trị đang tham chiếu đến – CASCADE © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 140 68
29. Khung nhìn (tt) ▪ Tại sao phải sử dụng khung nhìn? - Che dấu tính phức tạp của dữ liệu - Đơn giản hóa các câu truy vấn - Hiển thị dữ liệu dưới dạng tiện dụng nhất - An toàn dữ liệu © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 143 Định nghĩa khung nhìn ▪ Cú pháp CREATE VIEW AS DROP VIEW ▪ Bảng ảo này có - Danh sách thuộc tính trùng với các thuộc tính trong mệnh đề SELECT - Số dòng phụ thuộc vào điều kiện ở mệnh đề WHERE - Dữ liệu được lấy từ các bảng ở mệnh đề FROM © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 144 70
30. Truy vấn trên khung nhìn (tt) ▪ Có thể viết câu truy vấn dữ liệu từ khung nhìn và bảng SELECT DISTINCT GV.* FROM GV_HTTT GV, THAMGIADT PC WHERE GV.MAGV = PC.MAGV GV_HTTT  MABM='HTTT' (GIAOVIEN) KQ  GV_HTTT GV_HTTT.MAGV=THAMGIADT.MAGV THAMGIADT © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 147 Cập nhật trên khung nhìn ▪ Có thể dùng các câu lệnh INSERT, DELETE và UPDATE cho các khung nhìn đơn giản - Khung nhìn được xây dựng trên 1 bảng và có khóa chính của bảng ▪ Không thể cập nhật dữ liệu nếu - Khung nhìn có dùng từ khóa DISTINCT - Khung nhìn có sử dụng các hàm kết hợp - Khung nhìn có mệnh đề SELECT mở rộng - Khung nhìn được xây dựng từ bảng có RB trên cột - Khung nhìn được xây dựng từ nhiều bảng © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 148 72
31. Chỉ mục ▪ Chỉ mục trên thuộc tính A là một cấu trúc dữ liệu làm cho việc tìm kiếm mẫu tin có chứa A hiệu quả hơn SELECT * FROM GIAOVIEN Đọc 10.000 bộ WHERE MABM='HTTT' AND PHAI=‘Nu’ Đọc 200 bộ Bảng GIAOVIEN có 10.000 bộ Có 200 giáo viên làm việc cho bộ môn ‘HTTT’ Đọc 70 bộ © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 151 Chỉ mục (tt) ▪ Cú pháp CREATE INDEX ON ( ) DROP INDEX ▪ Ví dụ CREATE INDEX MABM_IND ON GIAOVIEN(MABM) CREATE INDEX MABM_PHAI_IND ON GIAOVIEN(MABM, PHAI) © Bộ môn HTTT - Khoa CNTT - Trường ĐH KHTN 152 74