›

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

pdf 148 trang huongle 9030
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:

  • pdfbai_giang_co_so_du_lieu_chuong_5_sql.pdf

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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 3
  4. Giớ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ìn Lý thuyết : Chuẩn SQL-92 - 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ộ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 - Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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ờ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 ( [ ], [ ], [ ] ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 9
  10. Ví dụ - Tạo bảng CREATE TABLE GIAOVIEN ( MaGV CHAR(9), HoTen NVARCHAR(50), Luong INT(20), Phai CHAR(3), NgaySinh DATETIME, SoNha NVARCHAR(10), Duong NVARCHAR(50), Quan NVARCHAR(50), ThanhPho NVARCHAR(50), GVQLCM CHAR(9), MaBM CHAR(9) ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 11
  12. Ví dụ - RBTV CREATE TABLE GIAOVIEN ( MAGV CHAR(9) PRIMARY KEY, HOTEN NVARCHAR(50), LUONG INT DEFAULT (10000), PHAI CHAR(3) CHECK (PHAI IN(‘Nam’, ‘Nu’)), NGAYSINH DATETIME, SONHA NVARCHAR(10), DUONG NVARCHAR(50), QUAN NVARCHAR(50), THANHPHO NVARCHAR(50), GVQLCM CHAR(9), MABM CHAR(9) ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 12
  13. Ví dụ - RBTV CREATE TABLE CONGVIEC ( CREATE TABLE DETAI ( MADT VARCHAR(10), MADT VARCHAR (10) PRIMARY KEY, STT INT, TENDT NVARCHAR (50) UNIQUE, TENCV NVARCHAR(50), KINHPHI INT, NGAYBD DATETIME, CAPQL NVARCHAR (50), NGAYKT DATETIME, NGAYBD DATETIME, NGAYKT DATETIME, PRIMARY KEY(MADT, STT) MACD VARCHAR (10), ) GVCNDT CHAR (9) ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 13
  14. Ví dụ - Đặt tên cho RBTV CREATE TABLE GIAOVIEN ( MAGV CHAR(9) CONSTRAINT PK_GV PRIMARY KEY, HOTEN NVARCHAR(50) CONSTRAINT NN_HOTEN NOT NULL, LUONG INT CONSTRAINT DE_LUONG DEFAULT (10000), PHAI CHAR(3) CONSTRAINT CK_PHAI CHECK (PHAI IN('Nam', 'Nu')) CONSTRAINT NN_PHAI NOT NULL, NGAYSINH DATETIME, SONHA NVARCHAR(10), DUONG NVARCHAR(50), QUAN NVARCHAR(50), THANHPHO NVARCHAR(50), GVQL CHAR(9), MABM CHAR(9) ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 14
  15. Ví dụ - Đặt tên cho RBTV CREATE TABLE CONGVIEC( MADT VARCHAR(10), STT INT, TENCV NVARCHAR(50), NGAYBD DATETIME, NGAYKT DATETIME, CONSTRAINT PK_CV PRIMARY KEY(MADT, STT), CONSTRAINT FK_CONGVIEC_DETAI FOREIGN KEY MADT REFERENCES DETAI(MADT) ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 [ ] Xóa cột ALTER TABLE DROP COLUMN ALTER TABLE ALTER COLUMN Mở rộng cột Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 16
  17. Lệnh sửa bảng (tt) Thêm RBTV ALTER TABLE ADD CONSTRAINT , CONSTRAINT , Xóa RBTV ALTER TABLE DROP Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 17
  18. Ví dụ - Thay đổi cấu trúc bảng ALTER TABLE GIAOVIEN ADD TUOI INT CONSTRAINT CK_TUOI CHECK (TUOI >= 23 AND TUOI <=60) NOT NULL ALTER TABLE GIAOVIEN DROP COLUMN HOTEN ALTER TABLE GIAOVIEN ALTER COLUMN HOTEN NVARCHAR(100) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 18
  19. Ví dụ - Thay đổi RBTV CREATE TABLE BOMON( Phải xác định NOT NULL MABM INT NOT NULL, trước khi tạo khóa chính TENBM NVARCHAR(50), PHONG CHAR(10), DIENTHOAI CHAR(15), TRUONGBM CHAR(9), MAKHOA CHAR(4), NGAYNHANCHUC DATETIME ) ALTER TABLE BOMON ADD CONSTRAINT PK_BOMON PRIMARY KEY (MABM), Cần có bảng CONSTRAINT FK_TRBOMON FOREIGN KEY (TRUONGBM) GIAOVIEN trước REFERENCES GIAOVIEN(MAGV), CONSTRAINT UNI_TENBM UNIQUE(TENBM), CONSTRAINT DF_NGAYNHANCHUC DEFAULT(GETDATE()) FOR NGAYNHANCHUC Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 20
  21. Lệnh xóa bảng (tt) GIÁOVIÊN MãGV HọTên Lương Phái NgàySinh SốNhà Đường Quận ThànhPhố GVQL MãBM BỘMÔN MãKhoa TrưởngBM NgàyNhậnChức ĐiệnThoại MãBM TênBM Phòng KHOA TrưởngKhoa NgàyNhậnChức TênKhoa NămTL Phòng ĐiệnThoại MãKhoa Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 25
  26. Truy vấn cơ bản (tt) SQL và ĐSQH SELECT FROM WHERE  SELECT L FROM R L (C (R)) WHERE C Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 26
  27. Ví dụ Lấy tất cả các cột của quan hệ kết quả SELECT * 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 27
  28. Mệnh đề SELECT 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)) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 28
  29. Mệnh đề SELECT (tt) Tên bí danh SELECT MAKHOA AS 'Mã khoa', TENKHOA AS 'Tên khoa', PHONG AS 'Mã phòng' FROM KHOA WHERE PHONG='I53' AND NAMTL = '1995' Mã khoa Tên khoa Mã phòng CNTT Công nghệ thông tin I53 Mã khoa, Tên khoa, Mã phòng( MANV,HONV,TENLOT,TENNV(PHG='I53'NamTL='1995' (KHOA))) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 29
  30. Mệnh đề SELECT (tt) Mở rộng SELECT MAGV, HOTEN, SONHA + ',' + DUONG + ',' + ',' + QUAN + ',' + THANHPHO AS 'DIA CHI' FROM GIAOVIEN WHERE PHAI='Nam' MAGV HOTEN DIA CHI GV001 Nguyễn Văn A 123 Phan Đăng Lưu, Q.Phú Nhuận, TP.Hồ Chí Minh MAGV,HOTEN,DIA CHI( MAGV,HOTEN,SONHA+DUONG+QUAN+THANHPHO(PHAI=‘Nam’(GIAOVIEN))) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 30
  31. 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))) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 31
  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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 32
  33. Ví dụ Cho biết MAGV và TENGV làm việc ở bộ môn „Hệ thống thông tin‟ R1  GIAOVIEN GIAOVIEN.MABM=BOMON.MABM BOMON KQ  MAGV, HOTEN (TENBM='Hệ thống thông tin' (R1)) SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE TENBM= N'Hệ thống thông tin' AND GIAOVIEN.MABM=BOMON.MABM Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 33
  34. Mệnh đề WHERE SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON TRUE Biểu thức WHERE TENBM = N'Hệ thống thông tin' luận lý AND AND GIAOVIEN.MABM = BOMON.MABM TRUE Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 34
  35. Mệnh đề WHERE (tt) Độ ưu tiên SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE (TENBM = N'Hệ thống thông tin' OR TENBM = N'Mạng máy tính') AND GIAOVIEN.MABM = BOMON.MABM Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 35
  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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 36
  37. Mệnh đề WHERE (tt) NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 SELECT MAGV, HOTEN FROM GIAOVIEN WHERE LUONG 30000 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 37
  38. Mệnh đề WHERE (tt) LIKE SELECT MAGV, HOTEN FROM GIAOVIEN WHERE HOTEN LIKE ‘Nguyen _ _ _ _’ HOTEN LIKE ‘nguyen _ _ _ _’ SELECT MAGV, HOTEN 4 ký tự bất kỳ FROM GIAOVIEN WHERE HOTEN LIKE ‘Nguyen %’ Chuỗi bất kỳ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 38
  39. 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’ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 39
  40. Mệnh đề WHERE (tt) ESCAPE MAGV HOTEN GV001 Nguyễn Văn A GV002 Nguyễn_Văn_A SELECT MAGV, HOTEN SELECT MAGV, HOTEN FROM GIAOVIEN FROM GIAOVIEN WHERE HOTEN LIKE N'Nguyễn_%’ WHERE HOTEN LIKE N'Nguyễn!_%' ESCAPE '!' MAGV HOTEN MAGV HOTEN GV001 Nguyễn Văn A GV001 Nguyễn Văn A GV002 Nguyễn_Văn_A GV002 Nguyễn_Văn_A Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 40
  41. Mệnh đề WHERE (tt) charlist MAGV HOTEN GV001 Nguyễn Văn A GV002 Hoàng Thị C SELECT * SELECT * FROM GIAOVIEN GV FROM GIAOVIEN GV WHERE GV.HOTEN LIKE N'[n]%' WHERE GV.HOTEN LIKE N'[nh]%' MAGV HOTEN MAGV HOTEN GV001 Nguyễn Văn A GV001 Nguyễn Văn A GV002 Hoàng Thị C Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 41
  42. Mệnh đề WHERE (tt) charlist MAGV HOTEN GV001 Nguyễn Văn A GV002 Hoàng Thị C SELECT * SELECT * FROM GIAOVIEN GV FROM GIAOVIEN GV WHERE GV.HOTEN LIKE N'[^h]%' WHERE GV.HOTEN LIKE N'[^nh]%' MAGV HOTEN MAGV HOTEN GV001 Nguyễn Văn A Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 42
  43. 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’ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 43
  44. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 44
  45. Mệnh đề WHERE (tt) NULL SELECT MAGV, HOTEN FROM GIAOVIEN WHERE GVQL IS NULL SELECT MAGV, HOTEN FROM GIAOVIEN WHERE GVQL IS NOT NULL Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 45
  46. Mệnh đề FROM Không sử dụng mệnh đề WHERE SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE TRUE MANV MAPHG GV001 1 GV001 4 GV001 5 GV002 1 GV002 4 GV002 5 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 46
  47. Mệnh đề FROM (tt) Tên bí danh SELECT MAGV, HOTEN, MAGV, HOTEN FROM GIAOVIEN, GIAOVIEN WHERE MAGV = GVQL SELECT QL.MAGV, QL.HOTEN, GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, GIAOVIEN QL WHERE QL.MAGV = GV.GVQL Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 47
  48. Ví dụ 1 Với những đề tài thuộc cấp quản lý „Thành phố‟, cho biết mã đề tài, đề tài thuộc về chủ đề nào, họ tên người chủ nghiệm đề tài cùng với ngày sinh và địa chỉ của người ấy Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 48
  49. Ví dụ 2 Tìm họ tên của giáo 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. Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 49
  50. Ví dụ 3 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 đó. Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 50
  51. Ví dụ 4 Tìm họ tên của những giáo viên được “Nguyen Thanh Tung” phụ trách trực tiếp. Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 51
  52. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 52
  53. Mệnh đề ORDER BY (tt) Ví dụ SELECT * FROM PHANCONG 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 53
  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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 54
  55. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 55
  56. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 56
  57. Ví dụ 5 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, - 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 57
  58. Ví dụ 6 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 58
  59. Ví dụ 7 Tìm những giáo viên không tham gia bất kỳ đề tài nào SELECT MAGV FROM GIAOVIEN EXCEPT SELECT MAGV FROM PHANCONG Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 59
  60. Truy vấn lồng SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE TENBM = N'Hệ thống thông tin' AND GIAOVIEN.MABM = BOMON.MABM SELECT Câu truy vấn cha (Outer query) FROM WHERE ( SELECT Câu truy vấn con FROM (Subquery) WHERE ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 60
  61. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 61
  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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 62
  63. Ví dụ - Lồng phân cấp SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE GIAOVIEN.MAGV = BOMON.TRUONGBM SELECT MAGV, HOTEN FROM GIAOVIEN SELECT MAGV, HOTEN WHERE MABM IN (‘001', ‘002', ‘004', ‘005‘, ‘007‘ ) FROM GIAOVIEN WHERE MAGV IN (SELECT TRUONGBM FROM BOMON Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 63
  64. Ví dụ 5 SELECT MAGV FROM GIAOVIEN WHERE HOTEN LIKE N'Nguyễn%' AND LUONG > 200000 UNION SELECT TRUONGBM FROM BOMON WHERE YEAR(NGAYNHANCHUC)>=1995 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 64
  65. Ví dụ 5 SELECT HOTEN FROM GIAOVIEN WHERE MAGV IN (SELECT MAGV FROM GIAOVIEN WHERE HOTEN LIKE N'Nguyễn%' AND LUONG > 200000) OR MAGV IN (SELECT TRUONGBM FROM BOMON WHERE YEAR(NGAYNHANCHUC)>=1995) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 65
  66. Ví dụ 7 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 PHANCONG) SELECT * FROM GIAOVIEN WHERE MAGV <> ALL(SELECT MAGV FROM PHANCONG) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 66
  67. Ví dụ 8 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 WHERE LUONG > 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 67
  68. Ví dụ 9 Tìm những giáo viên có lương lớn hơn lương của tất cả giáo viên thuộc bộ môn „Hệ thống thông tin‟ SELECT * FROM GIAOVIEN WHERE LUONG > ALL (SELECT LUONG FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND BM.TENBM = N'Hệ thống thông tin') Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 68
  69. Ví dụ 10 Tìm những trưởng bộ môn tham gia tối thiểu 1 đề tài SELECT * FROM GIAOVIEN WHERE MAGV IN (SELECT TRUONGBM FROM BOMON) AND MAGV IN (SELECT MAGV FROM PHANCONG) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 69
  70. Ví dụ - Lồng tương quan SELECT MAGV, HOTEN FROM GIAOVIEN, BOMON WHERE GIAOVIEN.MAGV = BOMON.TRUONGBM SELECT MAGV, HOTEN FROM GIAOVIEN GV WHERE EXISTS (SELECT * FROM BOMON BM WHERE BM.TRUONGBM = GV.MAGV) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 70
  71. Ví dụ 6 Tìm giáo viên trùng tên và cùng giới tính với giáo viên khác trong cùng bộ môn SELECT * FROM GIAOVIEN GV1 WHERE EXISTS (SELECT * FROM GIAOVIEN GV2 WHERE GV1.HOTEN LIKE GV2.HOTEN AND GV1.PHAI = GV2.PHAI AND GV1.MABM = GV2.MABM AND GV1.MAGV <> GV2.MAGV) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 71
  72. Ví dụ 7 Tìm những giáo viên không tham gia đề tài nào SELECT * FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT * FROM PHANCONG PC WHERE PC.MAGV = GV.MAGV) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 72
  73. Ví dụ 8 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 73
  74. Ví dụ 10 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 PHANCONG PC WHERE PC.MAGV = GV.MAGV) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 74
  75. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 75
  76. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 76
  77. Phép chia trong SQL (tt) Sử dụng EXCEPT để biểu diễn SELECT R1.A, R1.B, R1.C FROM R R1 WHERE NOT EXISTS (( SELECT S.D, S.E FROM S) EXCEPT ( SELECT R2.D, R2.E FROM R R2 WHERE R1.A=R2.A AND R1.B=R2.B AND R1.C=R2.C )) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 77
  78. 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 )) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 78
  79. Ví dụ 12 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 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(MAGV, MADT) - Tập chia: DETAI(MADT) - Tập kết quả: KQ(MAGV) - Kết KQ với GIAOVIEN để lấy ra TENGV Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 79
  80. Ví dụ 12 – Except SELECT DISTINCT GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, PHANCONG PC1 WHERE GV.MAGV = PC1.MAGV AND NOT EXISTS ((SELECT DT.MADT FROM DETAI DT) EXCEPT (SELECT PC2.MADT FROM PHANCONG PC2 WHERE PC2.MAGV = PC1.MAGV)) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 80
  81. Ví dụ 12 – Not Exists SELECT DISTINCT GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, PHANCONG PC1 WHERE GV.MAGV = PC1.MAGV AND NOT EXISTS (SELECT * FROM DETAI DT WHERE NOT EXISTS (SELECT * FROM PHANCONG PC2 WHERE PC2.MAGV = PC1.MAGV AND DT.MADT = PC2.MADT)) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 81
  82. 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 ]) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 82
  83. Ví dụ 12 – Count SELECT DISTINCT GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, PHANCONG PC1 WHERE GV.MAGV = PC1.MAGV GROUP BY GV.MAGV, GV.HOTEN HAVING COUNT(DISTINCT PC1.MADT) = (SELECT COUNT (MADT) FROM DETAI DT) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 83
  84. Ví dụ 12 – Count Tìm tên các giáo viên được phân công làm tất cả các đề tài có kinh phí trên 100 triệu? SELECT DISTINCT GV.MAGV, GV.HOTEN FROM GIAOVIEN GV, PHANCONG PC1 WHERE GV.MAGV = PC1.MAGV AND PC1.MADT IN (SELECT DT.MADT FROM DETAI WHERE KINHPHI > 100 GROUP BY GV.MAGV, GV.HOTEN HAVING COUNT(DISTINCT PC1.MADT) = (SELECT COUNT (MADT) FROM DETAI DT WHERE KINHPHI >100) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 84
  85. 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 85
  86. 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 hoặc HAVING Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 86
  87. Ví dụ 13 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 giáo viên SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM GIAOVIEN Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 87
  88. Ví dụ 14 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' Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 88
  89. Ví dụ 15 Tìm những giáo viên có lương thuộc 3 mức lương cao nhất SELECT * FROM GIAOVIEN GV1 WHERE 2 >= (SELECT COUNT(*) FROM GIAOVIEN GV2 WHERE GV2.LUONG > GV1.LUONG) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 89
  90. Ví dụ 16 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 90
  91. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 91
  92. Ví dụ 16 Cho biết số lượng giáo viên của từng bộ môn SELECT MABM, COUNT(*) 'Số lượng giáo viên' FROM GIAOVIEN GROUP BY MABM SELECT GV.MABM, COUNT(*) 'Số lượng giáo viên' FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM GROUP BY GV.MABM Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 92
  93. Ví dụ 16 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 PHANCONG PC GROUP BY PC.MAGV, PC.MADT Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 93
  94. Ví dụ 17 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 94
  95. Điều kiện trên nhóm Cú pháp SELECT FROM WHERE GROUP BY HAVING Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 95
  96. Ví dụ 17 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? SELECT PC.MAGV, PC.MADT, COUNT(*) AS 'Số lượng công việc' FROM PHANCONG PC GROUP BY PC.MAGV, PC.MADT HAVING COUNT(*) >= 2 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 96
  97. Ví dụ 18 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 PHANCONG PC GROUP BY PC.MAGV HAVING COUNT(DISTINCT MADT) >= 2 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 97
  98. Ví dụ 19 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 98
  99. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 99
  100. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 100
  101. Ví dụ 20 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 101
  102. Ví dụ 20 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 102
  103. Ví dụ 21 Tìm tên các giáo viên được phân công làm tất cả các đề tài SELECT PC.MAGV, COUNT(DISTINCT PC.MADT) AS 'Số lượng đề tài' FROM PHANCONG PC GROUP BY PC.MAGV HAVING COUNT(DISTINCT PC.MADT) = (SELECT COUNT(MADT) FROM DETAI) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 103
  104. 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 104
  105. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 105
  106. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 106
  107. Ví dụ 22 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 BM.TENBM, AVG(GV.LUONG) AS LUONG_TB FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM GROUP BY BM.MABM, BM.TENBM HAVING AVG(GV.LUONG)>=20000 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 107
  108. Ví dụ 18(tt) 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 BM.TENBM, LUONG_GV.LUONG_TB FROM BOMON BM, (SELECT MABM, AVG(LUONG) LUONG_TB FROM GIAOVIEN GROUP BY MABM) AS LUONG_GV WHERE BM.MABM = LUONG_GV.MABM Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 108
  109. Đ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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 109
  110. Ví dụ 20 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' Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 110
  111. Ví dụ 21 Tìm họ tên các giáo viên và tên các đề tài giáo viên tham gia nếu có SELECT DISTINCT GV.* FROM GIAOVIEN GV LEFT JOIN (PHANCONG PC JOIN DETAI DT ON PC.MADT = DT.MADT) ON GV.MAGV = PC.MAGV GIAOVIEN GIAOVIEN JOIN PHANCONG GV.MAGV= PC.MAGV mở rộng Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 111
  112. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 112
  113. Ví dụ 22 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 113
  114. Ví dụ 23 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 114
  115. Kết luận SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 115
  116. 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 116
  117. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 117
  118. Lệnh INSERT (tt) Cú pháp (thêm 1 dòng) INSERT INTO ( ) VALUES ( ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 118
  119. Ví dụ INSERT INTO PHANCONG(MAGV, MADT, STT, TENCV) VALUES('GV001', 'DT001', 1, N'PHÂN TÍCH') INSERT INTO PHANCONG(MADT, MAGV, STT) VALUES('DT001', 'GV001', 3) INSERT INTO PHANCONG VALUES('GV001', 'DT002', 1, N'PHÂN TÍCH YÊU CẦU') Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 119
  120. 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ị Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 120
  121. Lệnh INSERT (tt) Cú pháp (thêm nhiều dòng) INSERT INTO ( ) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 121
  122. Ví dụ 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 122
  123. Lệnh DELETE Dùng để xóa các dòng của bảng Cú pháp DELETE FROM [WHERE ] Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 123
  124. Ví dụ DELETE FROM GIAOVIEN WHERE HOTEN LIKE N'Trần%' DELETE FROM GIAOVIEN WHERE MAGV = 'GV001' DELETE FROM GIAOVIEN Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 124
  125. Ví dụ 24 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' Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 125
  126. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 126
  127. Lệnh DELETE (tt) MAGV 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 MAGV MADT STT PHUCAP KETQUA GV001 001 1 GV001 001 3 GV003 002 1 GV004 003 1 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 127
  128. Lệnh DELETE (tt) MABM TENBM HTTT Hệ thống thông tin CNPM Công nghệ phần mềm MMT Mạng máy tính KHMT Khoa học máy tính MANV HOTEN MABM GV001 Nguyễn Văn A NULLHTTT GV002 Trần Văn B NULLHTTT GV003 Trần Thị C CNPM GV004 Đặng Thị D MMT Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 128
  129. 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 ] Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 129
  130. Ví dụ UPDATE GIAOVIEN SET NGAYSINH='08/12/1965' WHERE MAGV='GV001' UPDATE GIAOVIEN SET LUONG=LUONG*1.1 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 130
  131. Ví dụ 25 Với mỗi giáo viên của bộ môn „Hệ thống thông tin‟, nâng lương của các giáo viên gấp 1.5 lần và gán giáo viên quản lý (GVQL) thành null UPDATE GIAOVIEN SET LUONG = LUONG*1.5, GVQL = NULL WHERE MABM = (SELECT MABM FROM BOMON WHERE TENBM = N'Hệ thống thông tin') Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 131
  132. Ví dụ Tăng 10% lương cho giáo viên có tham gia đề tài UPDATE GIAOVIEN SET LUONG = LUONG * 1.1 FROM PHANCONG PC WHERE PC.MAGV = GIAOVIEN.MAGV Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 132
  133. Ví dụ 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 PHANCONG PC WHERE PC.MADT = 'DT001' GROUP BY PC.MAGV, PC.MADT HAVING COUNT(*) >= 3) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 133
  134. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 134
  135. 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 - Định nghĩa - Truy vấn - Cập nhật Chỉ mục Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 135
  136. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 136
  137. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 137
  138. Đị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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 138
  139. Ví dụ CREATE VIEW GV_HTTT AS SELECT GV.* FROM GIAOVIEN GV WHERE BM.MABM = 'HTTT' CREATE VIEW THONGKE_BM AS SELECT BM.TENBM, COUNT(GV.MAGV) SL_GV, SUM(GV.LUONG) TONG_LUONG FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM GROUP BY BM.MABM, BM.TENBM Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 139
  140. 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 GV.HOTEN FROM GV_HTTT GV WHERE GV.MAGV = 'GV003' GV_HTTT  MABM='HTTT' (GIAOVIEN) HOTEN (MAGV='GV003' (GV_HTTT)) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 140
  141. 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, PHANCONG PC WHERE GV.MAGV = PC.MAGV GV_HTTT  MABM='HTTT' (GIAOVIEN) KQ  GV_HTTT GV_HTTT.MAGV=PHANCONG.MAGV PHANCONG Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 141
  142. 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 Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 142
  143. Cập nhật trên khung nhìn (tt) Sửa lại lương cho giáo viên mã „GV003‟ ở bộ môn 'Hệ thống thông tin' tăng lên 10% UPDATE GV_HTTT SET LUONG = LUONG * 1.1 WHERE MAGV = 'GV003' Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 143
  144. 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 Chỉ mục Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 144
  145. 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 70 bộ Có 200 giáo viên làm việc cho bộ môn ‘HTTT’ Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 145
  146. 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) Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 146
  147. 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ý? sẽ được tìm hiểu kỹ trong các môn học tiếp Hệ QTCSDL, CSDL NC. Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 147
  148. Cơ sở dữ liệu - Khoa CNTT - ĐH KHTN TPHCM 148