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

Chương 5SQLNội dung chi tiếtGiớ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)2Giới thiệuNgôn ngữ ĐSQH Cách thức truy vấn dữ liệuKhó khăn cho người sử dụngSQL (Structured Query Language)Ngôn ngữ cấp caoNgườ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ẩnSQL-86SQL-92SQL-993Giới thiệu (tt)SQL gồmĐịnh nghĩa dữ liệu (DDL)Thao tác dữ liệu (DML)Định nghĩa khung nhìnRàng buộc toàn vẹn Phân quyền và bảo mậtĐiều khiển giao tácSQL sử dụng thuật ngữBảng ~ quan hệCột ~ thuộc tínhDòng ~ bộLý thuyết : Chuẩn SQL-92Ví dụ : SQL Server 4Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuKiểu dữ liệuCác lệnh định nghĩa dữ liệuTruy vấn dữ liệu Cập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)5Định nghĩa dữ liệuLà 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ínhRàng buộc toàn vẹnChỉ mục trên mỗi quan hệ GồmCREATE 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 DATABASE6Kiểu dữ liệuSố (numeric)INTEGERSMALLINTNUMERIC, NUMERIC(p), NUMERIC(p,s)DECIMAL, DECIMAL(p), DECIMAL(p,s)REALDOUBLE PRECISIONFLOAT, FLOAT(p)7Kiể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ămTIME gồm giờ, phút và giâyTIMESTAMP gồm ngày và giờ8Lệnh tạo bảngĐể định nghĩa một bảngTên bảngCác thuộc tínhTên thuộc tínhKiểu dữ liệuCác RBTV trên thuộc tínhCú phápCREATE TABLE ( [], [],  [])9Ví dụ - Tạo bảngCREATE 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)10Lệnh tạo bảng (tt) NOT NULLNULLUNIQUEDEFAULTPRIMARY KEYFOREIGN KEY / REFERENCESCHECKĐặt tên cho RBTVCONSTRAINT 11Ví dụ - RBTVCREATE 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)12Ví dụ - RBTVCREATE 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))13Ví dụ - Đặt tên cho RBTVCREATE 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)14Ví dụ - Đặt tên cho RBTVCREATE 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))15Lệnh sửa bảngĐược dùng để Thay đổi cấu trúc bảng Thay đổi RBTVThêm cộtXóa cộtMở rộng cộtALTER TABLE ADD COLUMN []ALTER TABLE DROP COLUMN ALTER TABLE ALTER COLUMN 16Lệnh sửa bảng (tt)Thêm RBTVXóa RBTVALTER TABLE ADD CONSTRAINT , CONSTRAINT , ALTER TABLE DROP 17Ví dụ - Thay đổi cấu trúc bảngALTER TABLE NHANVIEN ADD NGHENGHIEP CHAR(20)ALTER TABLE NHANVIEN DROP COLUMN NGHENGHIEPALTER TABLE NHANVIEN ALTER COLUMN NGHENGHIEP CHAR(50)18Ví dụ - Thay đổi RBTVCREATE 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)19Lệnh xóa bảngĐược dùng để xóa cấu trúc bảngTất cả dữ liệu của bảng cũng bị xóaCú phápVí dụDROP TABLE DROP TABLE NHANVIENDROP TABLE PHONGBANDROP TABLE PHANCONG20Lệnh xóa bảng (tt)NHANVIENTENNVHONVTENLOTMANVNGSINHDCHIPHAILUONGMA_NQLPHGPHONGBANTRPHGTENPHGMAPHGNG_NHANCHUC21Lệ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ẳnCú phápVí dụCREATE DOMAIN AS CREATE DOMAIN Kieu_Ten AS VARCHAR(30)22Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số kiểu truy vấn khácCập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)23Truy vấn dữ liệuLà ngôn ngữ rút trích dữ liệu thỏa một số điều kiện nào đóDựa trênCho phép 1 bảng có nhiều dòng trùng nhauBảng là bag  quan hệ là setPhép toán ĐSQHMột số bổ sung24Truy vấn cơ bảnGồm 3 mệnh đềTên các cột cần được hiển thị trong kết quả truy vấnTên các bảng liên quan đến câu truy vấnBiểu thức boolean xác định dòng nào sẽ được rút tríchNối các biểu thức: AND, OR, và NOT Phép toán:  ,  ,  ,  ,  , , LIKE và BETWEENSELECT FROM WHERE 25Truy vấn cơ bản (tt)SELECT FROM WHERE SELECT LFROM RWHERE CL (C (R))SQL và ĐSQH26Ví dụSELECT *FROM NHANVIENWHERE PHG=5Lấy tất cả các cột của quan hệ kết quảTENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManh27Mệnh đề SELECTSELECT MANV, HONV, TENLOT, TENNVFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’TENNVHONVTungNguyenHungNguyenTENLOTThanhManh333445555987987987MANV28Mệnh đề SELECT (tt)SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TENFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’TENHOTungNguyenHungNguyenTEN LOTThanhManh333445555987987987MANVTên bí danh29Mệnh đề SELECT (tt)SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’FROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’HO TENNguyen Thanh TungNguyen Manh Hung333445555987987987MANVMở rộng30Mệnh đề SELECT (tt)SELECT MANV, LUONG*1.1 AS ‘LUONG10%’FROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’LUONG10%3300027500333445555987987987MANVMở rộng31Mệnh đề SELECT (tt)SELECT LUONGFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’Loại bỏ các dòng trùng nhau Tốn chi phí Người dùng muốn thấyLUONG30000250002500038000LUONG300002500038000SELECT DISTINCT LUONGFROM NHANVIENWHERE PHG=5 AND PHAI=‘Nam’32Ví dụCho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’33Mệnh đề WHERESELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGBiểu thức luận lýTRUETRUE34Mệnh đề WHERE (tt)SELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHGĐộ ưu tiên35Mệnh đề WHERE (tt)SELECT MANV, TENNV FROM NHANVIENWHERE LUONG>20000 AND LUONGFROM WHERE ORDER BY 50Mệnh đề ORDER BY (tt)Ví dụSELECT MA_NVIEN, SODAFROM PHANCONGORDER BY MA_NVIEN DESC, SODASODA1030999887777999887777MA_NVIEN103098798798798765432198798798710209876543213098765432151Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácCập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)52Phép toán tập hợp trong SQLSQL có cài đặt các phép toánHội (UNION)Giao (INTERSECT)Trừ (EXCEPT)Kết quả trả về là tập hợpLoại bỏ các bộ trùng nhauĐể giữ lại các bộ trùng nhauUNION ALLINTERSECT ALLEXCEPT ALL53Phép toán tập hợp trong SQL (tt)Cú phápSELECT FROM WHERE UNION [ALL]SELECT FROM WHERE SELECT FROM WHERE INTERSECT [ALL]SELECT FROM WHERE SELECT FROM WHERE EXCEPT [ALL]SELECT FROM WHERE 54Ví dụ 5Cho 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’55Ví dụ 6Tìm nhân viên có người thân cùng tên và cùng giới tính56Ví dụ 7Tìm những nhân viên không có thân nhân nào57Truy vấn lồngSELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGSELECT FROM WHERE ( SELECT FROM WHERE )Câu truy vấn cha (Outer query)Câu truy vấn con (Subquery)58Truy vấn lồng (tt)Các câu lệnh SELECT có thể lồng nhau ở nhiều mứcCâ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 logicMệ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 INALLANY hoặc SOMEKiểm tra sự tồn tạiEXISTSNOT EXISTS59Truy vấn lồng (tt)Có 2 loại truy vấn lồngLồng phân cấpMệ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 chaKhi thực hiện, câu truy vấn con sẽ được thực hiện trướcLồng tương quanMệ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 chaKhi 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 cha60Ví dụ - Lồng phân cấpSELECT MANV, TENNV FROM NHANVIENWHERE PHG IN ( SELECT MAPHG FROM DIADIEM_PHG WHERE DIADIEM=‘TP HCM’ )(1, 5)SELECT MANV, TENNV FROM NHANVIEN, DIADIEM_PHGWHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG61Ví dụ 562Ví dụ 7Tìm những nhân viên không có thân nhân nào63Ví dụ 8Tì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 464Ví dụ 9Tì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 465Ví dụ 10Tìm những trưởng phòng có tối thiểu một thân nhân66Ví dụ - Lồng tương quanSELECT MANV, TENNV FROM NHANVIEN, PHONGBANWHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHGSELECT MANV, TENNV FROM NHANVIENWHERE EXISTS ( SELECT * FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG )67Ví dụ 6Tìm nhân viên có người thân cùng tên và cùng giới tính68Ví dụ 7Tìm những nhân viên không có thân nhân nào69Ví dụ 8Tì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 470Ví dụ 10Tìm những trưởng phòng có tối thiểu một thân nhân71Nhận xét IN và EXISTSIN 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 chaEXISTSKhông cần có thuộc tính, hằng số hay biểu thức nào khác đứng trướcKhông nhất thiết liệt kê tên thuộc tính ở mệnh đề SELECT của truy vấn conNhững câu truy vấn có = ANY hay IN đều có thể chuyển thành câu truy vấn có EXISTS72Phép chia trong SQLR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 RABaaaaaaaaCDabaababbE13111111RDEaSb11ABCaaRSaibi73Phép chia trong SQL (tt)Sử dụng NOT EXISTS để biểu diễnSELECT R1.A, R1.B, R1.CFROM R R1WHERE 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 ))74Ví dụ 11Tìm tên các nhân viên được phân công làm tất cả các đồ ánTì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àmTậ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 TENNV75Ví 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 đồ án76Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácCập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)77Hàm kết hợpCOUNTCOUNT(*) đếm số dòngCOUNT() đếm số giá trị khác NULL của thuộc tínhCOUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tínhMINMAXSUMAVGCác hàm kết hợp được đặt ở mệnh đề SELECT78Ví dụ 12Tì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ên79Ví dụ 13Cho biết số lượng nhân viên của phòng ‘Nghien cuu’ 80Ví dụ 14Cho biết số lượng nhân viên của từng phòng banSL_NV5433PHG11TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhTamTran07/31/1972543 MTL Q1Nu250005HangBui07/19/196833 NTH Q1Nu380004453453453999887777333445555987654321ThanhNgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhQuangTran04/08/1969980 LHP Q5Nam250004VinhPham11/10/1945450 TV HNNam550001987987987888665555987654321NULLHongVan81Gom nhómCú phápSau khi gom nhómMỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhómSELECT FROM WHERE GROUP BY 82Ví dụ 14Cho biết số lượng nhân viên của từng phòng ban83Ví dụ 15Vớ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 giaSODATHOIGIAN132.527.5123456789123456789MA_NVIEN210.0310.03334455553334455551010.03334455552020.01035.0888665555987987987305.09879879873020.09876543212015.0987654321120.0453453453220.045345345384Ví dụ 16Cho biết những nhân viên tham gia từ 2 đề án trở lênSODATHOIGIAN132.527.5123456789123456789MA_NVIEN210.0310.03334455553334455551010.03334455552020.01035.0888665555987987987305.09879879873020.09876543212015.0987654321120.0453453453220.0453453453bị loại ra85Điều kiện trên nhómCú phápSELECT FROM WHERE GROUP BY HAVING 86Ví dụ 16Cho biết những nhân viên tham gia từ 2 đề án trở lên87Ví dụ 17Cho 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 2000088Nhận xétMệ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 BYMệnh đề HAVINGSử 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 89Nhậ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 đề SELECT90Ví dụ 18Tìm những phòng ban có lương trung bình cao nhất91Ví dụ 19Tìm 3 nhân viên có lương cao nhất92Ví dụ 12Tìm tên các nhân viên được phân công làm tất cả các đồ án93Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuTruy vấn cơ bảnTập hợp, so sánh tập hợp và truy vấn lồngHàm kết hợp và gom nhómMột số dạng truy vấn khácCập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)94Một số dạng truy vấn khácTruy vấn con ở mệnh đề FROM Điều kiện kết ở mệnh đề FROMPhép kết tự nhiên Phép kết ngoàICấu trúc CASE95Truy vấn con ở mệnh đề FROMKết quả trả về của một câu truy vấn phụ là một bảngBảng trung gian trong quá trình truy vấnKhông có lưu trữ thật sựCú phápSELECT FROM R1, R2, () AS tên_bảngWHERE 96Ví dụ 18Cho 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 2000097Điều kiện kết ở mệnh đề FROMKết bằngKết ngoàiSELECT FROM R1 [INNER] JOIN R2 ON WHERE SELECT FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON WHERE 98Ví dụ 20Tìm mã và tên các nhân viên làm việc tại phòng ‘Nghien cuu’99Ví dụ 21Tì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ó100Cấu trúc CASECho phép kiểm tra điều kiện và xuất thông tin theo từng trường hợpCú phápCASE WHEN THEN WHEN THEN [ELSE ]END101Ví dụ 22Cho 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)102Ví dụ 23Cho biết họ tên các nhân viên và năm về hưu103Kết luậnSELECT FROM [WHERE ][GROUP BY ][HAVING ][ORDER BY ]104Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuCập nhật dữ liệu Thêm (insert)Xóa (delete)Sửa (update)Khung nhìn (view)Chỉ mục (index)105Lệnh INSERTDùng để thêm 1 hay nhiều dòng vào bảngĐể thêm dữ liệuTên quan hệDanh sách các thuộc tính cần thêm dữ liệuDanh sách các giá trị tương ứng106Lệnh INSERT (tt)Cú pháp (thêm 1 dòng)INSERT INTO ()VALUES ()107Ví dụINSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV)VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’)INSERT INTO NHANVIENVALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, ’12/30/1952’, ’98 HV’, ‘Nam’, ‘37000’, 4)INSERT INTO NHANVIEN(HONV, TENLOT, TENNV, MANV, DCHI)VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, NULL)108Lệnh INSERT (tt)Nhận xétThứ tự các giá trị phải trùng với thứ tự các cộtCó thể thêm giá trị NULL ở những thuộc tính không là khóa chính và NOT NULLCâu lệnh INSERT sẽ gặp lỗi nếu vi phạm RBTVKhóa chínhTham chiếuNOT NULL - các thuộc tính có ràng buộc NOT NULL bắt buộc phải có giá trị109Lệnh INSERT (tt)Cú pháp (thêm nhiều dòng)INSERT INTO () 110Ví 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 TENPHG111Lệnh DELETE Dùng để xóa các dòng của bảngCú phápDELETE FROM [WHERE ]112Ví dụDELETE FROM NHANVIENWHERE HONV=‘Tran’DELETE FROM NHANVIENWHERE MANV=‘345345345’DELETE FROM NHANVIEN113Ví dụ 24Xóa đi những nhân viên ở phòng ‘Nghien cuu’114Lệnh DELETE (tt) Nhận xétSố lượng số dòng bị xóa phụ thuộc vào điều kiện ở mệnh đề WHERENế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óaLệnh DELETE có thể gây ra vi phạm RB tham chiếuKhông cho xóaXóa luôn những dòng có giá trị đang tham chiếu đếnCASCADEĐặt NULL cho những giá trị tham chiếu115Lệnh DELETE (tt) TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam400005HungNguyen09/15/1962Ba Ria VTNam380005333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhHangBui07/19/196833 NTH Q1Nu380004999887777987654321NgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhVinhPham11/10/1945450 TV HNNam550001888665555NULLVanSODATHOIGIANMA_NVIEN1010.03334455552020.08886655553020.0987654321120.0453453453TamTran07/31/1972543 MTL Q1Nu250005453453453333445555ThanhQuangTran04/08/1969980 LHP Q5Nam250004987987987987654321Hong1035.0987987987305.0987987987116Lệnh DELETE (tt) TENNVHONVNGSINHDCHIPHAILUONGPHGTungNguyen12/08/1955638 NVC Q5Nam40000HungNguyen09/15/1962Ba Ria VTNam38000333445555987987987MANVMA_NQL888665555333445555TENLOTThanhManhHangBui07/19/196833 NTH Q1Nu380004999887777987654321NgocNhuLe07620/1951219 TD Q3Nu430004987654321888665555QuynhVinhPham11/10/1945450 TV HNNam550001888665555NULLVanTamTran07/31/1972543 MTL Q1Nu25000555453453453333445555ThanhQuangTran04/08/1969980 LHP Q5Nam250004987987987987654321HongNULLNULLNULL05/22/1988333445555Nghien cuu5NG_NHANCHUCMA_NVIEN01/01/199506/19/1981987987987888665555TENPHGMAPHGDieu hanh4Quan ly1117Lệnh UPDATE Dùng để thay đổi giá trị của thuộc tính cho các dòng của bảngCú phápUPDATE SET =, =, [WHERE ]118Ví dụUPDATE NHANVIENSET NGSINH=’08/12/1965’WHERE MANV=‘333445555’DELETE NHANVIENSET LUONG=LUONG*1.1119Ví dụ 25Vớ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 5UPDATE DEANSET DIADIEM_DA=’Vung Tau’, PHONG=5WHERE MADA=10120Lệnh UPDATE Nhận xétNhững dòng thỏa điều kiện tại mệnh đề WHERE sẽ được cập nhật giá trị mớiNế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ậtLệnh UPDATE có thể gây ra vi phạm RB tham chiếuKhông cho sửaSửa luôn những dòng có giá trị đang tham chiếu đếnCASCADE121Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuCập nhật dữ liệu Khung nhìn (view)Định nghĩaTruy vấnCập nhậtChỉ mục (index)122Khung nhìnBảng là một quan hệ được tổ chức lưu trữ vật lý trong CSDLKhung 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ácCó thể truy vấn hay cập nhật thông qua khung nhìn123Khung 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ấnHiển thị dữ liệu dưới dạng tiện dụng nhấtAn toàn dữ liệu124Định nghĩa khung nhìnCú phápBả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 đề SELECTSố dòng phụ thuộc vào điều kiện ở mệnh đề WHEREDữ liệu được lấy từ các bảng ở mệnh đề FROMCREATE VIEW AS DROP VIEW 125Ví dụCREATE VIEW NV_P5 AS SELECT MANV, HONV, TENLOT, TENVN FROM NHANVIEN WHERE PHG=5CREATE VIEW TONGLNG_SLNV_PB AS SELECT MAPHG, TENPB, COUNT(*) AS SLNV, SUM(LUONG) AS TONGLNG FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG126Truy vấn trên khung nhìnTuy 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ìnSELECT TENNVFROM NV_P5WHERE HONV LIKE ‘Nguyen’NV_P5  MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN)) TENNV (HONV=‘Nguyen’ (NV_P5))127Truy 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ảngSELECT HONV, TENVN, TENDA, THOIGIANFROM NV_P5, PHANCONG, DEANWHERE MANV=MA_NVIEN AND SODA=MADANV_P5  MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN))TMP  NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADADEANTENNV,TENDA,THOIGIAN(TMP)128Cập nhật trên khung nhìnCó thể dùng các câu lệnh INSERT, DELETE và UPDATE cho các khung nhìn đơn giảnKhung nhìn được xây dựng trên 1 bảng và có khóa chính của bảngKhông thể cập nhật dữ liệu nếuKhung nhìn có dùng từ khóa DISTINCTKhung nhìn có sử dụng các hàm kết hợp Khung nhìn có mệnh đề SELECT mở rộngKhung nhìn được xây dựng từ bảng có RB trên cộtKhung nhìn được xây dựng từ nhiều bảng129Cậ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_P5SET HONV=‘Pham’WHERE MANV= ‘123456789’130Nội dung chi tiếtGiới thiệuĐịnh nghĩa dữ liệuTruy vấn dữ liệuCập nhật dữ liệu Khung nhìn (view)Chỉ mục (index)Định nghĩaChọn lựa chỉ mục131Chỉ mụcChỉ 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ơnSELECT *FROM NHANVIENWHERE PHG=5 AND PHAI=‘Nu’Bảng NHANVIEN có 10.000 bộCó 200 nhân viên làm việc cho phòng 5Đọc 10.000 bộĐọc 200 bộĐọc 70 bộ132Chỉ mục (tt)Cú phápVí dụCREATE INDEX ON ()CREATE INDEX PHG_IND ON NHANVIEN(PHG)CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI)DROP INDEX 133Chỉ mục (tt)Nhận xétTìm kiếm nhanh trong trường hợp so sánh với hằng số và phép kếtLà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ụcTruy xuất đĩa nhiềuChọn lựa cài đặt chỉ mục hợp lý???134Ví dụXét quan hệ PHANCONG(MA_NVIEN, SODA, THOIGIAN)Giả sửPHANCONG được lưu trữ trong 10 blockChi phí để đọc toàn bộ dữ liệu của PHANCONG là 10Trung 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àmDữ liệu được trải đều trong 10 blockChi phí để tìm một nhân viên hay một đề án là 3Khi sử dụng chỉ mục Chi phí đọc hay cập nhật chỉ mụcThao tác thêm cần 2 lần truy xuất đĩa135Ví dụ (tt)Giả sử có 3 thao tác được thực hiện thường xuyênQ1Q2Q3SELECT SODA, THOIGIANFROM PHANCONGWHERE MA_NVIEN=‘123456789’SELECT MANVFROM PHANCONGWHERE SODA=1 AND THOIGIAN=20.5INSERT INTO PHANCONGVALUES ( 123456789’, 1, 20.5)136Ví dụ (tt)Bảng so sánh chi phíThao tácKhông có chỉ mục Chỉ mục trên MA_NVIEN Chỉ mục trên SODA Chỉ mục trên cả 2 thuộc tínhQ1Q2Q310102410410444462 + 8p1 + 8p24 + 6p24 + 6p16 - 2p1 – 2p2Chí phí TBKhoảng thời gian thực hiện Q1 là p1Khoảng thời gian thực hiện Q2 là p2Khoảng thời gian thực hiện Q3 là 1 - p1 - p2 137Bài tập về nhàBài tập6.1.1 đến 6.1.4 / 252 - 2536.2.1 đến 6.2.3 / 262 - 2636.3.1 đến 6.3.3 / 274 - 2756.3.7 đến 6.3.10 / 275 - 2766.4.1, 6.4.2, 6.4.6, 6.4.7 / 284 - 2856.5.1, 6.5.2 / 290 - 2916.7.1 đến 6.7.6 / 311 -312138139