Bài giảng Nhập môn Cơ Sở Dữ Liệu - Chương 5: Ngôn ngữ SQL

nNhập môn Cơ sở dữ liệu - Khoa CNTT92Nộ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)Nhập môn Cơ sở dữ liệu - Khoa CNTT93Mộ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 CASENhập môn Cơ sở dữ liệu - Khoa CNTT94Truy 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 Nhập môn Cơ sở dữ liệu - Khoa CNTT95Đ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 Nhập môn Cơ sở dữ liệu - Khoa CNTT96Ví dụ 20Tìm mã và tên các nhân viên làm việc tại phòng ‘Nghien cuu’Nhập môn Cơ sở dữ liệu - Khoa CNTT97Ví 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óNhập môn Cơ sở dữ liệu - Khoa CNTT98Cấ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 ]ENDNhập môn Cơ sở dữ liệu - Khoa CNTT99Ví 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)Nhập môn Cơ sở dữ liệu - Khoa CNTT100Ví dụ 23Cho biết họ tên các nhân viên và năm về hưuNhập môn Cơ sở dữ liệu - Khoa CNTT101Kết luậnSELECT FROM [WHERE ][GROUP BY ][HAVING ][ORDER BY ]Nhập môn Cơ sở dữ liệu - Khoa CNTT102Nộ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)Nhập môn Cơ sở dữ liệu - Khoa CNTT103Lệ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 ứngNhập môn Cơ sở dữ liệu - Khoa CNTT104Lệnh INSERT (tt)Cú pháp (thêm 1 dòng)INSERT INTO ()VALUES ()Nhập môn Cơ sở dữ liệu - Khoa CNTT105Ví dụINSERT INTO NHANVIEN(HONV, TENDEM, 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, TENDEM, TENNV, MANV, DCHI)VALUES (‘Le’, ‘Van’, ‘Tuyen’, ‘635635635’, NULL)Nhập môn Cơ sở dữ liệu - Khoa CNTT106Lệ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ịNhập môn Cơ sở dữ liệu - Khoa CNTT107Lệnh INSERT (tt)Cú pháp (thêm nhiều dòng)INSERT INTO ()	Nhập môn Cơ sở dữ liệu - Khoa CNTT108Ví 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 TENPHGNhập môn Cơ sở dữ liệu - Khoa CNTT109Lệnh DELETE Dùng để xóa các dòng của bảngCú phápDELETE FROM [WHERE ]Nhập môn Cơ sở dữ liệu - Khoa CNTT110Ví dụDELETE FROM NHANVIENWHERE HONV=‘Tran’DELETE FROM NHANVIENWHERE MANV=‘345345345’DELETE FROM NHANVIENNhập môn Cơ sở dữ liệu - Khoa CNTT111Lệ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ếuNhập môn Cơ sở dữ liệu - Khoa CNTT112Lệ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.0987987987Nhập môn Cơ sở dữ liệu - Khoa CNTT113Lệ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 ly1Nhập môn Cơ sở dữ liệu - Khoa CNTT114Lệ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 ]Nhập môn Cơ sở dữ liệu - Khoa CNTT115Ví dụUPDATE NHANVIENSET NGSINH=’08/12/1965’WHERE MANV=‘333445555’DELETE NHANVIENSET LUONG=LUONG*1.1Nhập môn Cơ sở dữ liệu - Khoa CNTT116Ví 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=10Nhập môn Cơ sở dữ liệu - Khoa CNTT117Lệ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 đếnCASCADENhập môn Cơ sở dữ liệu - Khoa CNTT118Nộ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)Nhập môn Cơ sở dữ liệu - Khoa CNTT119Khung 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ìnNhập môn Cơ sở dữ liệu - Khoa CNTT120Khung 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ệuNhập môn Cơ sở dữ liệu - Khoa CNTT121Đị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 Nhập môn Cơ sở dữ liệu - Khoa CNTT122Ví dụCREATE VIEW NV_P5 AS	SELECT MANV, HONV, TENDEM, 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 TENPHGNhập môn Cơ sở dữ liệu - Khoa CNTT123Truy 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, TENDEM, TENNV (PHG=5 (NHANVIEN)) TENNV (HONV=‘Nguyen’ (NV_P5))Nhập môn Cơ sở dữ liệu - Khoa CNTT124Truy 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, TENDEM, TENNV (PHG=5 (NHANVIEN))TMP  NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADADEANTENNV,TENDA,THOIGIAN(TMP)Nhập môn Cơ sở dữ liệu - Khoa CNTT125Cậ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ảngNhập môn Cơ sở dữ liệu - Khoa CNTT126Cậ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’Nhập môn Cơ sở dữ liệu - Khoa CNTT127Nộ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ụcNhập môn Cơ sở dữ liệu - Khoa CNTT128Chỉ 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 GT=‘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ộNhập môn Cơ sở dữ liệu - Khoa CNTT129Chỉ mục (tt)Cú phápVí dụCREATE INDEX ON ()CREATE INDEX PHG_IND ON NHANVIEN(PHG)CREATE INDEX PHG_GT_IND ON NHANVIEN(PHG, GT)DROP INDEX Nhập môn Cơ sở dữ liệu - Khoa CNTT130Chỉ 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ý???Nhập môn Cơ sở dữ liệu - Khoa CNTT131Ví 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 đĩaNhập môn Cơ sở dữ liệu - Khoa CNTT132Ví 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)Nhập môn Cơ sở dữ liệu - Khoa CNTT133Ví 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 Nhập môn Cơ sở dữ liệu - Khoa CNTT134Nhập môn Cơ sở dữ liệu - Khoa CNTT135