›
›

Bài giảng Cơ sở dữ liệu - Chương 5: SQL

ppt 139 trang phuongnguyen 8650
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Cơ sở dữ liệu - Chương 5: SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pptbai_giang_co_so_du_lieu_chuong_5_sql.ppt

Nội dung text: Bài giảng Cơ sở dữ liệu - Chương 5: SQL

  1. Chương 5 SQL
  2. Nội dung chi tiết ¡ Giới thiệu ¡ Định nghĩa dữ liệu ¡ Truy vấn dữ liệu ¡ Cập nhật dữ liệu ¡ Khung nhìn (view) ¡ Chỉ mục (index) 2
  3. Giới thiệu ¡ Ngôn ngữ ĐSQH - Cách thức truy vấn dữ liệu - Khó khăn cho người sử dụng ¡ SQL (Structured Query Language) - Ngôn ngữ cấp cao - Người sử dụng chỉ cần đưa ra nội dung cần truy vấn - Được phát triển bởi IBM (1970s) - Được gọi là SEQUEL - Được ANSI công nhận và phát triển thành chuẩn Ÿ SQL-86 Ÿ SQL-92 Ÿ SQL-99 3
  4. Giới thiệu (tt) ¡ SQL gồm - Định nghĩa dữ liệu (DDL) - Thao tác dữ liệu (DML) Lý thuyết : Chuẩn SQL-92 - Định nghĩa khung nhìn - Ràng buộc toàn vẹn Ví dụ : SQL Server - Phân quyền và bảo mật - Điều khiển giao tác ¡ SQL sử dụng thuật ngữ - Bảng ~ quan hệ - Cột ~ thuộc tính - Dòng ~ bộ 4
  5. Nội dung chi tiết ¡ Giới thiệu ¡ Định nghĩa dữ liệu - Kiểu dữ liệu - Các lệnh định nghĩa dữ liệu ¡ Truy vấn dữ liệu ¡ Cập nhật dữ liệu ¡ Khung nhìn (view) ¡ Chỉ mục (index) 5
  6. Định nghĩa dữ liệu ¡ Là ngôn ngữ mô tả - Lược đồ cho mỗi quan hệ - Miền giá trị tương ứng của từng thuộc tính - Ràng buộc toàn vẹn - Chỉ mục trên mỗi quan hệ ¡ Gồm - CREATE TABLE (tạo bảng) - DROP TABLE (xóa bảng) - ALTER TABLE (sửa bảng) - CREATE DOMAIN (tạo miền giá trị) - CREATE DATABASE - 6
  7. Kiểu dữ liệu ¡ Số (numeric) - INTEGER - SMALLINT - NUMERIC, NUMERIC(p), NUMERIC(p,s) - DECIMAL, DECIMAL(p), DECIMAL(p,s) - REAL - DOUBLE PRECISION - FLOAT, FLOAT(p) 7
  8. Kiểu dữ liệu (tt) ¡ Chuỗi ký tự (character string) - CHARACTER, CHARACTER(n) - CHARACTER VARYING(x) ¡ Chuỗi bit (bit string) - BIT, BIT(x) - BIT VARYING(x) ¡ Ngày giờ (datetime) - DATE gồm ngày, tháng và năm - TIME gồm giờ, phút và giây - TIMESTAMP gồm ngày và giờ 8
  9. Lệnh tạo bảng ¡ Để định nghĩa một bảng - Tên bảng - Các thuộc tính Ÿ Tên thuộc tính Ÿ Kiểu dữ liệu Ÿ Các RBTV trên thuộc tính ¡ Cú pháp CREATE TABLE ( [ ], [ ], [ ] ) 9
  10. Ví dụ - Tạo bảng CREATE TABLE NHANVIEN ( MANV CHAR(9), HONV VARCHAR(10), TENLOT VARCHAR(20), TENNV VARCHAR(10), NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3), LUONG INT, MA_NQL CHAR(9), PHG INT ) 10
  11. Lệnh tạo bảng (tt) ¡ - NOT NULL - NULL - UNIQUE - DEFAULT - PRIMARY KEY - FOREIGN KEY / REFERENCES - CHECK ¡ Đặt tên cho RBTV CONSTRAINT 11
  12. Ví dụ - RBTV CREATE TABLE NHANVIEN ( HONV VARCHAR(10) NOT NULL, TENLOT VARCHAR(20) NOT NULL, TENNV VARCHAR(10) NOT NULL, MANV CHAR(9) PRIMARY KEY, NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3) CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT DEFAULT (10000), MA_NQL CHAR(9), PHG INT ) 12
  13. Ví dụ - RBTV CREATE TABLE PHONGBAN ( TENPB VARCHAR(20) UNIQUE, MAPHG INT NOT NULL, TRPHG CHAR(9), NG_NHANCHUC DATETIME DEFAULT (GETDATE()) ) CREATE TABLE PHANCONG ( MA_NVIEN CHAR(9) FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), SODA INT REFERENCES DEAN(MADA), THOIGIAN DECIMAL(3,1) ) 13
  14. Ví dụ - Đặt tên cho RBTV CREATE TABLE NHANVIEN ( HONV VARCHAR(10) CONSTRAINT NV_HONV_NN NOT NULL, TENLOT VARCHAR(20) NOT NULL, TENNV VARCHAR(10) NOT NULL, MANV CHAR(9) CONSTRAINT NV_MANV_PK PRIMARY KEY, NGSINH DATETIME, DCHI VARCHAR(50), PHAI CHAR(3) CONSTRAINT NV_PHAI_CHK CHECK (PHAI IN (‘Nam’, ‘Nu’)), LUONG INT CONSTRAINT NV_LUONG_DF DEFAULT (10000), MA_NQL CHAR(9), PHG INT ) 14
  15. Ví dụ - Đặt tên cho RBTV CREATE TABLE PHANCONG ( MA_NVIEN CHAR(9), SODA INT, THOIGIAN DECIMAL(3,1), CONSTRAINT PC_MANVIEN_SODA_PK PRIMARY KEY (MA_NVIEN, SODA), CONSTRAINT PC_MANVIEN_FK FOREIGN KEY (MA_NVIEN) REFERENCES NHANVIEN(MANV), CONSTRAINT PC_SODA_FK FOREIGN KEY (SODA) REFERENCES DEAN(MADA) ) 15
  16. Lệnh sửa bảng ¡ Được dùng để - Thay đổi cấu trúc bảng - Thay đổi RBTV ¡ Thêm cột ALTER TABLE ADD COLUMN [ ] ¡ Xóa cột ALTER TABLE DROP COLUMN ALTER TABLE ALTER COLUMN ¡ Mở rộng cột 16
  17. Lệnh sửa bảng (tt) ¡ Thêm RBTV ALTER TABLE ADD CONSTRAINT , CONSTRAINT , ¡ Xóa RBTV ALTER TABLE DROP 17
  18. Ví dụ - Thay đổi cấu trúc bảng ALTER TABLE NHANVIEN ADD NGHENGHIEP CHAR(20) ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEP ALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP CHAR(50) 18
  19. Ví dụ - Thay đổi RBTV CREATE TABLE PHONGBAN ( TENPB VARCHAR(20), MAPHG INT NOT NULL, TRPHG CHAR(9), NG_NHANCHUC DATETIME ) ALTER TABLE PHONGBAN ADD CONSTRAINT PB_MAPHG_PK PRIMARY KEY (MAPHG), CONSTRAINT PB_TRPHG FOREIGN KEY (TRPHG) REFERENCES NHANVIEN(MANV), CONSTRAINT PB_NGNHANCHUC_DF DEFAULT (GETDATE()) FOR (NG_NHANCHUC), CONSTRAINT PB_TENPB_UNI UNIQUE (TENPB) 19
  20. Lệnh xóa bảng ¡ Được dùng để xóa cấu trúc bảng - Tất cả dữ liệu của bảng cũng bị xóa ¡ Cú pháp DROP TABLE ¡ Ví dụ DROP TABLE NHANVIEN DROP TABLE PHONGBAN DROP TABLE PHANCONG 20
  21. Lệnh xóa bảng (tt) NHANVIEN HONV TENLOT TENNV MANV NGSINH DCHI PHAI LUONG MA_NQL PHG PHONGBAN TENPHG MAPHG TRPHG NG_NHANCHUC 21
  22. Lệnh tạo miền giá trị ¡ Tạo ra một kiểu dữ liệu mới kế thừa những kiểu dữ liệu có sẳn ¡ Cú pháp CREATE DOMAIN AS ¡ Ví dụ CREATE DOMAIN Kieu_Ten AS VARCHAR(30) 22
  23. 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ố kiểu truy vấn khác ¡ Cập nhật dữ liệu ¡ Khung nhìn (view) ¡ Chỉ mục (index) 23
  24. Truy vấn dữ liệu ¡ Là ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đó ¡ Dựa trên Phép toán ĐSQH Một số bổ sung - Cho phép 1 bảng có nhiều dòng trùng nhau - Bảng là bag quan hệ là set 24
  25. Truy vấn cơ bản ¡ Gồm 3 mệnh đề SELECT FROM WHERE - Ÿ Tên các cột cần được hiển thị trong kết quả truy vấn - Ÿ Tên các bảng liên quan đến câu truy vấn - Ÿ Biểu thức boolean xác định dòng nào sẽ được rút trích Ÿ Nối các biểu thức: AND, OR, và NOT Ÿ Phép toán: , , , , , , LIKE và BETWEEN 25
  26. Truy vấn cơ bản (tt) ¡ SQL và ĐSQH SELECT FROM WHERE SELECT L FROM L ( R C (R)) WHERE C 26
  27. Ví dụ Lấy tất cả các cột của quan hệ kết quả SELECT * FROM NHANVIEN WHERE PHG=5 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 27
  28. Mệnh đề SELECT SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HONV TENLOT TENNV 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 28
  29. Mệnh đề SELECT (tt) Tên bí danh SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN LOT TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 29
  30. Mệnh đề SELECT (tt) Mở rộng SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV HO TEN 333445555 Nguyen Thanh Tung 987987987 Nguyen Manh Hung 30
  31. Mệnh đề SELECT (tt) Mở rộng SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ MANV LUONG10% 333445555 33000 987987987 27500 31
  32. Mệnh đề SELECT (tt) Loại bỏ các dòng trùng nhau SELECT DISTINCTLUONG LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ LUONG 30000 - Tốn chi phí 25000 2500038000 - Người dùng muốn thấy 38000 32
  33. Ví dụ ¡ Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’ 33
  34. Mệnh đề WHERE SELECT MANV, TENNV Biểu thức luận lý FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG TRUE TRUE 34
  35. Mệnh đề WHERE (tt) Độ ưu tiên SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG 35
  36. Mệnh đề WHERE (tt) BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>20000 AND LUONG<30000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 36
  37. Mệnh đề WHERE (tt) NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 37
  38. Mệnh đề WHERE (tt) LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen _ _ _ _’ SELECT MANV, TENNV Ký tự bất kỳ FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen %’ Chuỗi bất kỳ 38
  39. Mệnh đề WHERE (tt) NOT LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen’ SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT NOT LIKE ‘Nguyen’ 39
  40. Mệnh đề WHERE (tt) ESCAPE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘% Nguyens_%’ ESCAPE ‘s’ ‘Nguyen_’ 40
  41. Mệnh đề WHERE (tt) Ngày giờ SELECT MANV, TENNV FROM NHANVIEN WHERE NGSINH 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’ 41
  42. Mệnh đề WHERE (tt) NULL - Sử dụng trong trường hợp Ÿ Không biết (value unknown) Ÿ Không thể áp dụng (value inapplicable) Ÿ Không tồn tại (value withheld) - Những biểu thức tính toán có liên quan đến giá trị NULL sẽ cho ra kết quả là NULL Ÿ x có giá trị là NULL Ÿ x + 3 cho ra kết quả là NULL Ÿ x + 3 là một biểu thức không hợp lệ trong SQL - Những biểu thức so sánh có liên quan đến giá trị NULL sẽ cho ra kết quả là UNKNOWN Ÿ x = 3 cho ra kết quả là UNKNOWN Ÿ x = 3 là một so sánh không hợp lệ trong SQL 42
  43. Mệnh đề WHERE (tt) NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL 43
  44. Mệnh đề FROM Không sử dụng mệnh đề WHERE SELECT MANV, MAPHG FROM NHANVIEN, PHONGBAN WHERE TRUE MANV MAPHG 333445555 1 333445555 4 333445555 5 987987987 1 987987987 4 987987987 5 44
  45. Mệnh đề FROM (tt) Tên bí danh SELECT TENPHG, DIADIEM FROM PHONGBAN,PHONGBAN ASDDIEM_PHG PB, DDIEM_PHG AS DD WHERE MAPHG=MAPHGPB.MAPHG=DD.MAPHG SELECT TENNV, NGSINH,NV.NGSINH, TENTN, TENTN, NGSINH TN.NGSINH FROM NHANVIEN,NHANVIEN NV,THANNHAN THANNHAN TN WHERE MANV=MA_NVIEN 45
  46. Ví dụ 1 ¡ Với những đề án ở ‘Ha Noi’, cho biết mã đề án, mã phòng ban chủ trì đề án, họ tên trưởng phòng cùng với ngày sinh và địa chỉ của người ấy 46
  47. Ví dụ 2 ¡ Tìm họ tên của nhân viên phòng số 5 có tham gia vào đề án “Sản phẩm X” với số giờ làm việc trên 10 giờ 47
  48. Ví dụ 3 ¡ Tìm họ tên của từng nhân viên và người phụ trách trực tiếp nhân viên đó 48
  49. Ví dụ 4 ¡ Tìm họ tên của những nhân viên được “Nguyen Thanh Tung” phụ trách trực tiếp 49
  50. Mệnh đề ORDER BY ¡ Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó ¡ Cú pháp SELECT FROM WHERE ORDER BY - ASC: tăng (mặc định) - DESC: giảm 50
  51. Mệnh đề ORDER BY (tt) ¡ Ví dụ SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA MA_NVIEN SODA 999887777 10 999887777 30 987987987 10 987987987 30 987654321 10 987654321 20 987654321 30 51
  52. 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) 52
  53. Phép toán tập hợp trong SQL ¡ SQL có cài đặt các phép toán - Hội (UNION) - Giao (INTERSECT) - Trừ (EXCEPT) ¡ Kết quả trả về là tập hợp - Loại bỏ các bộ trùng nhau - Để giữ lại các bộ trùng nhau Ÿ UNION ALL Ÿ INTERSECT ALL Ÿ EXCEPT ALL 53
  54. Phép toán tập hợp trong SQL (tt) ¡ Cú pháp SELECT FROM WHERE UNION [ALL] SELECT FROM WHERE SELECT FROM WHERE INTERSECT [ALL] SELECT FROM WHERE SELECT FROM WHERE EXCEPT [ALL] SELECT FROM WHERE 54
  55. Ví dụ 5 ¡ Cho biết các mã đề án có - Nhân viên với họ là ‘Nguyen’ tham gia hoặc, - Trưởng phòng chủ trì đề án đó với họ là ‘Nguyen’ 55
  56. Ví dụ 6 ¡ Tìm nhân viên có người thân cùng tên và cùng giới tính 56
  57. Ví dụ 7 ¡ Tìm những nhân viên không có thân nhân nào 57
  58. Truy vấn lồng SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT Câu truy vấn cha (Outer query) FROM WHERE ( SELECT Câu truy vấn con FROM (Subquery) WHERE ) 58
  59. 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 59
  60. 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 60
  61. Ví dụ - Lồng phân cấp SELECT MANV, TENNV FROM NHANVIEN, DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN ((1, 5) SELECT MAPHG FROM DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ ) 61
  62. Ví dụ 5 62
  63. Ví dụ 7 ¡ Tìm những nhân viên không có thân nhân nào 63
  64. Ví dụ 8 ¡ Tìm những nhân viên có lương lớn hơn lương của ít nhất một nhân viên phòng 4 64
  65. Ví dụ 9 ¡ Tìm những nhân viên có lương lớn hơn lương của tất cả nhân viên phòng 4 65
  66. Ví dụ 10 ¡ Tìm những trưởng phòng có tối thiểu một thân nhân 66
  67. Ví dụ - Lồng tương quan SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS ( SELECT * FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG ) 67
  68. Ví dụ 6 ¡ Tìm nhân viên có người thân cùng tên và cùng giới tính 68
  69. Ví dụ 7 ¡ Tìm những nhân viên không có thân nhân nào 69
  70. Ví dụ 8 ¡ Tìm những nhân viên có lương lớn hơn lương của ít nhất một nhân viên phòng 4 70
  71. Ví dụ 10 ¡ Tìm những trưởng phòng có tối thiểu một thân nhân 71
  72. Nhận xét IN và EXISTS ¡ IN - IN - Thuộc tính ở mệnh đề SELECT của truy vấn con phải có cùng kiểu dữ liệu với thuộc tính ở mệnh đề WHERE của truy vấn cha ¡ EXISTS - Không cần có thuộc tính, hằng số hay biểu thức nào khác đứng trước - Không nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn con - Những câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS 72
  73. Phép chia trong SQL R A B C D E S D E RS A B C a a 1 a 1 a  bi ai a  a 1 b 1  a  a  b 1  a  a 1  a  b 3  a  a 1  a  b 1  a  b 1 ¡ RS là tập các giá trị ai trong R sao cho không có giá trị bi nào trong S làm cho bộ (ai, bi) không tồn tại trong R 73
  74. 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 )) 74
  75. Ví dụ 11 ¡ Tìm tên các nhân viên được phân công làm tất cả các đồ án - Tìm tên các nhân viên mà không có đề án nào là không được phân công làm - Tập bị chia: PHANCONG(MA_NVIEN, SODA) - Tập chia: DEAN(MADA) - Tập kết quả: KQ(MA_NVIEN) - Kết KQ với NHANVIEN để lấy ra TENNV 75
  76. Ví dụ 11 (tt) ¡ Tìm tên các nhân viên được phân công làm tất cả các đồ án 76
  77. 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) 77
  78. Hàm kết hợp ¡ COUNT - COUNT(*) đếm số dòng - COUNT( ) đếm số giá trị khác NULL của thuộc tính - COUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tính ¡ MIN ¡ MAX ¡ SUM ¡ AVG ¡ Các hàm kết hợp được đặt ở mệnh đề SELECT 78
  79. Ví dụ 12 ¡ Tìm tổng lương, lương cao nhất, lương thấp nhất và lương trung bình của các nhân viên 79
  80. Ví dụ 13 ¡ Cho biết số lượng nhân viên của phòng ‘Nghien cuu’ 80
  81. Ví dụ 14 ¡ Cho biết số lượng nhân viên của từng phòng ban PHG SL_NV 5 3 4 3 1 1 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 81
  82. Gom nhóm ¡ Cú pháp SELECT FROM WHERE GROUP BY ¡ Sau khi gom nhóm - Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm 82
  83. Ví dụ 14 ¡ Cho biết số lượng nhân viên của từng phòng ban 83
  84. Ví dụ 15 ¡ Với mỗi nhân viên cho biết mã số, họ tên, số lượng đề án và tổng thời gian mà họ tham gia MA_NVIEN SODA THOIGIAN 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 888665555 20 20.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 453453453 1 20.0 453453453 2 20.0 84
  85. Ví dụ 16 ¡ Cho biết những nhân viên tham gia từ 2 đề án trở lên MA_NVIEN SODA THOIGIAN 123456789 1 32.5 123456789 2 7.5 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 888665555 20 20.0 bị loại ra 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 453453453 1 20.0 453453453 2 20.0 85
  86. Điều kiện trên nhóm ¡ Cú pháp SELECT FROM WHERE GROUP BY HAVING 86
  87. Ví dụ 16 ¡ Cho biết những nhân viên tham gia từ 2 đề án trở lên 87
  88. Ví dụ 17 ¡ Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000 88
  89. Nhận xét ¡ Mệnh đề GROUP BY - Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY ¡ Mệnh đề HAVING - Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó - Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ - Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện 89
  90. Nhận xét (tt) ¡ Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING - (1) Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE - (2) Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY - (3) Áp dụng các hàm kết hợp cho mỗi nhóm - (4) Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING - (5) Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT 90
  91. Ví dụ 18 ¡ Tìm những phòng ban có lương trung bình cao nhất 91
  92. Ví dụ 19 ¡ Tìm 3 nhân viên có lương cao nhất 92
  93. Ví dụ 12 ¡ Tìm tên các nhân viên được phân công làm tất cả các đồ án 93
  94. 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) 94
  95. 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 95
  96. 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 96
  97. Ví dụ 18 ¡ Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000 97
  98. Đ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 98
  99. Ví dụ 20 ¡ Tìm mã và tên các nhân viên làm việc tại phòng ‘Nghien cuu’ 99
  100. Ví dụ 21 ¡ Tìm họ tên các nhân viên và tên các đề án nhân viên tham gia nếu có 100
  101. Cấu trúc CASE ¡ Cho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợp ¡ Cú pháp CASE WHEN THEN WHEN THEN [ELSE ] END 101
  102. Ví dụ 22 ¡ Cho biết họ tên các nhân viên đã đến tuổi về hưu (nam 60 tuổi, nữ 55 tuổi) 102
  103. Ví dụ 23 ¡ Cho biết họ tên các nhân viên và năm về hưu 103
  104. Kết luận SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 104
  105. Nội dung chi tiết ¡ Giới thiệu ¡ Định nghĩa dữ liệu ¡ Truy vấn dữ liệu ¡ Cập nhật dữ liệu - Thêm (insert) - Xóa (delete) - Sửa (update) ¡ Khung nhìn (view) ¡ Chỉ mục (index) 105
  106. 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 106
  107. Lệnh INSERT (tt) ¡ Cú pháp (thêm 1 dòng) INSERT INTO ( ) VALUES ( ) 107
  108. Ví dụ INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV) VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’) INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI) VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, NULL) INSERT INTO NHANVIEN VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, ’12/30/1952’, ’98 HV’, ‘Nam’, ‘37000’, 4) 108
  109. Lệnh INSERT (tt) ¡ 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 và NOT NULL - Câu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTV Ÿ Khóa chính Ÿ Tham chiếu Ÿ NOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị 109
  110. Lệnh INSERT (tt) ¡ Cú pháp (thêm nhiều dòng) INSERT INTO ( ) 110
  111. Ví dụ CREATE TABLE THONGKE_PB ( TENPHG VARCHAR(20), SL_NV INT, LUONG_TC INT ) INSERT INTO THONGKE_PB(TENPHG, SL_NV, LUONG_TC) SELECT TENPHG, COUNT(MANV), SUM(LUONG) FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG 111
  112. Lệnh DELETE ¡ Dùng để xóa các dòng của bảng ¡ Cú pháp DELETE FROM [WHERE ] 112
  113. Ví dụ DELETE FROM NHANVIEN WHERE HONV=‘Tran’ DELETE FROM NHANVIEN WHERE MANV=‘345345345’ DELETE FROM NHANVIEN 113
  114. Ví dụ 24 ¡ Xóa đi những nhân viên ở phòng ‘Nghien cuu’ 114
  115. 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 115
  116. Lệnh DELETE (tt) MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 MA_NVIEN SODA THOIGIAN 333445555 10 10.0 888665555 20 20.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 453453453 1 20.0 116
  117. Lệnh DELETE (tt) TENPHG MAPHG MA_NVIEN NG_NHANCHUC Nghien cuu 5 333445555 05/22/1988 Dieu hanh 4 987987987 01/01/1995 Quan ly 1 888665555 06/19/1981 MANV HONV TENLOT TENNV NGSINH DCHI PHAI LUONG MA_NQL PHG 333445555 Nguyen Thanh Tung 12/08/1955 638 NVC Q5 Nam 40000 888665555 NULL5 987987987 Nguyen Manh Hung 09/15/1962 Ba Ria VT Nam 38000 333445555 NULL5 453453453 Tran Thanh Tam 07/31/1972 543 MTL Q1 Nu 25000 333445555 NULL5 999887777 Bui Ngoc Hang 07/19/1968 33 NTH Q1 Nu 38000 987654321 4 987654321 Le Quynh Nhu 07620/1951 219 TD Q3 Nu 43000 888665555 4 987987987 Tran Hong Quang 04/08/1969 980 LHP Q5 Nam 25000 987654321 4 888665555 Pham Van Vinh 11/10/1945 450 TV HN Nam 55000 NULL 1 117
  118. 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 ] 118
  119. Ví dụ UPDATE NHANVIEN SET NGSINH=’08/12/1965’ WHERE MANV=‘333445555’ DELETE NHANVIEN SET LUONG=LUONG*1.1 119
  120. Ví dụ 25 ¡ Với đề án có mã số 10, hãy thay đổi nơi thực hiện đề án thành ‘Vung Tau’ và phòng ban phụ trách là phòng 5 UPDATE DEAN SET DIADIEM_DA=’Vung Tau’, PHONG=5 WHERE MADA=10 120
  121. 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 121
  122. Nội dung chi tiết ¡ Giới thiệu ¡ Định nghĩa dữ liệu ¡ Truy vấn dữ liệu ¡ Cập nhật dữ liệu ¡ Khung nhìn (view) - Định nghĩa - Truy vấn - Cập nhật ¡ Chỉ mục (index) 122
  123. Khung nhìn ¡ Bảng là một quan hệ được tổ chức lưu trữ vật lý trong CSDL ¡ Khung nhìn cũng là một quan hệ - Không được lưu trữ vật lý (bảng ảo) - Không chứa dữ liệu - Được định nghĩa từ những bảng khác - Có thể truy vấn hay cập nhật thông qua khung nhìn 123
  124. 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 124
  125. Đị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 125
  126. Ví dụ CREATE VIEW NV_P5 AS SELECT MANV, HONV, TENLOT, TENVN FROM NHANVIEN WHERE PHG=5 CREATE VIEW TONGLNG_SLNV_PB AS SELECT MAPHG, TENPB, COUNT(*) AS SLNV, SUM(LUONG) AS TONGLNG FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG 126
  127. Truy vấn trên khung nhìn ¡ Tuy không chứa dữ liệu nhưng có thể thực hiện các câu truy vấn trên khung nhìn SELECT TENNV FROM NV_P5 WHERE HONV LIKE ‘Nguyen’ NV_P5  MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN)) TENNV (HONV=‘Nguyen’ (NV_P5)) 127
  128. 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 HONV, TENVN, TENDA, THOIGIAN FROM NV_P5, PHANCONG, DEAN WHERE MANV=MA_NVIEN AND SODA=MADA NV_P5  MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN)) TMP  NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADADEAN TENNV,TENDA,THOIGIAN(TMP) 128
  129. 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 129
  130. Cập nhật trên khung nhìn (tt) ¡ Sửa lại họ cho nhân viên mã ‘123456789’ ở phòng 5 là ‘Pham’ UPDATE NV_P5 SET HONV=‘Pham’ WHERE MANV= ‘123456789’ 130
  131. Nội dung chi tiết ¡ Giới thiệu ¡ Định nghĩa dữ liệu ¡ Truy vấn dữ liệu ¡ Cập nhật dữ liệu ¡ Khung nhìn (view) ¡ Chỉ mục (index) - Định nghĩa - Chọn lựa chỉ mục 131
  132. 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 NHANVIEN Đọc 10.000 bộ WHERE PHG=5 AND PHAI=‘Nu’ Đọc 200 bộ Bảng NHANVIEN có 10.000 bộ Đọc 70 bộ Có 200 nhân viên làm việc cho phòng 5 132
  133. Chỉ mục (tt) ¡ Cú pháp CREATE INDEX ON ( ) DROP INDEX ¡ Ví dụ CREATE INDEX PHG_IND ON NHANVIEN(PHG) CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI) 133
  134. Chỉ mục (tt) ¡ Nhận xét - Tìm kiếm nhanh trong trường hợp so sánh với hằng số và phép kết - Làm chậm đi các thao tác thêm, xóa và sửa - Tốn chi phí Ÿ Lưu trữ chỉ mục Ÿ Truy xuất đĩa nhiều ¡ Chọn lựa cài đặt chỉ mục hợp lý??? 134
  135. Ví dụ ¡ Xét quan hệ - PHANCONG(MA_NVIEN, SODA, THOIGIAN) ¡ Giả sử - PHANCONG được lưu trữ trong 10 block Ÿ Chi phí để đọc toàn bộ dữ liệu của PHANCONG là 10 - Trung bình một nhân viên tham gia 3 đề án và một đề án có khoảng 3 nhân viên làm Ÿ Dữ liệu được trải đều trong 10 block Ÿ Chi phí để tìm một nhân viên hay một đề án là 3 - Khi sử dụng chỉ mục Ÿ Chi phí đọc hay cập nhật chỉ mục - Thao tác thêm cần 2 lần truy xuất đĩa 135
  136. Ví dụ (tt) ¡ Giả sử có 3 thao tác được thực hiện thường xuyên - Q1 SELECT SODA, THOIGIAN FROM PHANCONG WHERE MA_NVIEN=‘123456789’ - Q2 SELECT MANV FROM PHANCONG WHERE SODA=1 AND THOIGIAN=20.5 - Q3 INSERT INTO PHANCONG VALUES ( 123456789’, 1, 20.5) 136
  137. Ví dụ (tt) ¡ Bảng so sánh chi phí Không có Chỉ mục trên Chỉ mục Chỉ mục trên Thao tác chỉ mục MA_NVIEN trên SODA cả 2 thuộc tính Q1 10 4 10 4 Q2 10 10 4 4 Q3 2 4 4 6 Chí phí TB 2 + 8p1 + 8p2 4 + 6p2 4 + 6p1 6 - 2p1 – 2p2 Khoảng thời gian thực hiện Q1 là p1 Khoảng thời gian thực hiện Q2 là p2 Khoảng thời gian thực hiện Q3 là 1 - p1 - p2 137
  138. Bài tập về nhà ¡ Bài tập - 6.1.1 đến 6.1.4 / 252 - 253 - 6.2.1 đến 6.2.3 / 262 - 263 - 6.3.1 đến 6.3.3 / 274 - 275 - 6.3.7 đến 6.3.10 / 275 - 276 - 6.4.1, 6.4.2, 6.4.6, 6.4.7 / 284 - 285 - 6.5.1, 6.5.2 / 290 - 291 - 6.7.1 đến 6.7.6 / 311 -312 138