Giáo trình Ngôn ngữ SQL (Structured Query Language)

180 trang phuongnguyen 2990

Download

Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Ngôn ngữ SQL (Structured Query Language)", để 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:

giao_trinh_ngon_ngu_sql_structured_query_language.pdf

Nội dung text: Giáo trình Ngôn ngữ SQL (Structured Query Language)

ỦỐỘY BAN NHÂN DÂN THÀNH PH HÀ N I SỞƯỄỘ B U CHÍNH VI N THÔNG HÀ N I GIÁO TRÌNH NGÔN NGỮ SQL (Mã số giáo trình: 3CD3) HÀ NỘ I. 2005 HÀ NỘ I, 12-2004
LỜỞẦ I M Đ U Ngôn ngữ SQL (Structured Query Language) đ ượ c s ử d ụ ng trong hầế u h t các h ệảịơởữệể qu n tr c s d li u đ truy v ấ n và s ửổơởữ a đ i c s d liệ u. Ngôn ng ữ SQL h ỗ tr ợ các truy v ấ n d ự a trên các phép toán đ ạ i s ố quan hệồờ , đ ng th i cũng ch ứ a các l ệửổơởữệ nh s a đ i c s d li u và mô t ả lượồơởữệ c đ c s d li u. Nh ưậ v y, SQL v ừ a là m ộ t ngôn ng ữ thao tác d ữ liệ u, v ừ a là m ộ t ngôn ng ữ đ ị nh nghĩa d ữ li ệ u. Ngoài ra SQL cũng tiêu chuẩ n hoá nhi ề u l ệ nh c ơ s ở d ữ li ệ u khác. Có nhiề u phiên b ả n khác nhau c ủ a SQL. Tr ướ c tiên, có ba b ả n chuẩ n. Đó là ANSI (American National Standards Institute) SQL. Sau đó đế n năm 1992, b ả n chu ẩ n SQL-92 ra đ ờ i g ọ i là SQL2. G ầ n đây nh ấ t, chuẩ n SQL-99 (tr ướ c đó g ọ i là SQL3) m ở r ộ ng SQL2 v ớ i các đ ặ c tr ư ng quan hệ - đ ố i t ượ ng và m ộ t s ố kh ả năng m ớ i khác. Ngoài ra còn có nhi ề u phiên bảủ n c a SQL đ ượ c các nhà bán các h ệảịơởữệả qu n tr c s d li u s n xuấ t. Các phiên b ả n này có t ấ t c ả các kh ả năng c ủ a chu ẩ n ANSI nguyên gố c và chúng cũng phù h ợ p v ớ i các m ở r ộ ng c ủ a SQL cũng nh ư các tính chấ t c ủ a chu ẩ n SQL-99. Trong giáo trình này chúng tôi trình bày d ự a trên chuẩ n SQL-99. Giáo trình g ồ m ba ch ươ ng: Chươ ng 1: SQL c ơ b ả n, trình bày các truy v ấ n c ơ b ả n trên các b ả ng cơởữệ s d li u, các ki ểữệơả u d li u c b n trong SQL và cách t ạơởữ o c s d liệ u đ ơ n gi ả n trong SQL Chươ ng 2: Các ràng bu ộ c và các trigger. Ch ươ ng này trình bày các loạ i ràng bu ộ c: ràng bu ộ c mi ề n, ràng bu ộ c khóa, ràng bu ộ c toàn v ẹ n th ự c thể , ràng bu ộ c toàn v ẹ n tham chi ế u, các ràng bu ộ c khác và cách th ể hi ệ n chúng trong SQL. Chươ ng 3: L ậ p trình v ớ i SQL, trình bày các ph ươ ng pháp l ậ p trình trong SQL: lậ p trình nhúng, SQL đ ộ ng, các hàm và các th ủ t ụ c PSM, s ử dụ ng giao di ệ n g ọ i. Ngoài ra, ch ươ ng này còn đ ề c ậ p đ ế n v ấ n đ ề an toàn trên cơ s ở d ữ li ệ u SQL. Cuố i m ỗ i ch ươ ng có t ổ ng k ế t các v ấ n đ ề trình bày trong ch ươ ng và mộốậểểượ t s bài t p. Đ hi u đ c giáo trình này b ạọầả n đ c c n ph i có các ki ế n thứ c v ề c ơ s ở d ữ li ệ u quan h ệ . Do hạ n ch ế v ề th ờ i gian và kinh nghi ệ m, ch ắ c ch ắ n giáo trình v ẫ n còn nhiề u thi ế u sót. Mong các b ạ n đ ọ c góp ý, phê bình. Chúng tôi xin c ả m ơn tr ướ c và h ứ a s ẽ ti ế p thu đ ể hoàn thi ệ n giáo trình h ơ n. 2
- Tên môn họ c: Ngôn ng ữ SQL. - Mã số môn h ọ c: 3CD3 - Thờ i gian: 45 ti ế t (lí thuyế t + thực hành) - Mụ c tiêu: H ướ ng d ẫ n h ọ c viên s ử d ụ ng thành th ạ o ngôn ng ữ truy v ấ n SQL. - Nhữ ng ki ếứầượ n th c c n đ c trang b ịướơởữệ tr c: C s d li u quan h ệ . - Nộ i dung môn h ọ c: Chươ ng I: CƠẢỀ B N V SQL. Chươ ng II: CÁC RÀNG BUỘ C VÀ TRIGGER. Chươ ng III: LẬ P TRÌNH - Đố i t ượ ng h ọ c: Các l ậ p trình viên. - Biên soạ n: B ộ môn Các h ệ th ố ng thông tin, Khoa Công ngh ệ thông tin, Trườ ng ĐH Công Ngh ệ , ĐHQG Hà N ộ i. 3
LỜỞẦ I M Đ U 2 CHƯƠ NG I: SQL C Ơ B Ả N 8 1.1 CÁC TRUY VẤƠẢ N Đ N GI N TRONG SQL. 8 1.1.1 Phép chiế u trong SQL 9 1.1.2 Phép chọ n trong SQL 11 1.1.3 So sánh các xâu 13 1.1.4 Ngày tháng và thờ i gian 14 1.1.5 Các giá trị NULL và các so sánh bao hàm NULL. 15 1.1.6 Giá trị lôgic UNKNOWN 16 1.1.7 Sắ p th ứ t ự d ữ li ệ u ra 17 1.1.8 Các hàm thông dụ ng trong SQL 18 1.2 CÁC TRUY VẤỒỀƠỘỆ N BAO G M NHI U H N M T QUAN H 20 1.2.1 Tích và nố i trong SQL 20 1.2.2 Làm rõ nghĩa các thuộ c tính 21 1.2.3 Các biế n b ộ 22 1.2.4 Phép hợ p, phép giao, phép tr ừ c ủ a các truy v ấ n 23 1.3 CÁC TRUY VẤ N CON 25 1.3.1 Các truy vấ n con t ạ o ra các giá tr ị vô h ướ ng 26 1.3.2 Các điề u ki ệ n có bao hàm các quan h ệ 27 1.3.3 Các điề u ki ệ n có bao hàm các b ộ 28 1.3.4 Các truy vấ n con t ươ ng quan v ớ i nhau 29 1.3.5 Các truy vấ n con trong m ệ nh đ ề FROM 30 1.3.6 Các biể u th ứ c n ố i c ủ a SQL 31 1.3.7 Nố i t ự nhiên (Natural Join) 32 1.3.8 Nố i ngoài 33 1.4 CÁC PHÉP TOÁN QUAN HỆẦỦ Đ Y Đ 34 1.4.1 Loạ i b ỏ trùng l ặ p 34 1.4.2 Trùng lặ p trong phép h ợ p, phép giao và phép tr ừ 35 1.4.3 Nhóm và sự k ế t h ợ p trong SQL 36 1.4.4 Các phép toán nhóm 36 1.4.5 Nhóm 38 1.4.6 Các mệ nh đ ề HAVING 40 1.5 SỬỔƠỞỮỆ A Đ I C S D LI U 42 1.5.1 Chèn 42 1.5.2 Xóa 44 1.5.3 Cậ p nh ậ t 44 1.6 ĐỊ NH NGHĨA M Ộ T L ƯỢ C Đ Ồ QUAN H Ệ TRONG SQL 45 1.6.1 Các kiể u d ữ li ệ u 46 4
1.6.2 Các khai báo bả ng đ ơ n gi ả n 47 1.6.4 Các giá trị ng ầ m đ ị nh 48 1.6.5 Các chỉ s ố 49 1.6.6 Nhậ p môn v ề vi ệ c l ự a ch ọ n các ch ỉ s ố 50 1.7 KHUNG NHÌN (VIEW) 51 1.7.1 Khai báo các khung nhìn 51 1.7.2 Truy vấ n các khung nhìn 52 1.7.3 Đặ t tên l ạ i các thu ộ c tính 53 1.7.4 Sử a đ ổ i các khung nhìn 54 1.7.5 Giả i thích các truy v ấ n có ch ứ a các khung nhìn 57 1.8 TỔ NG K Ế T CH ƯƠ NG I 60 MỘỐẬ T S BÀI T P 62 CHƯƠ NG II: CÁC RÀNG BU Ộ C VÀ CÁC TRIGGER 66 2.1 KHÓA VÀ KHÓA NGOÀI 67 2.1.1 Mô tả khoá chính 67 2.1.2 Các khoá đượ c mô t ả v ớ i UNIQUE 69 2.1.3 Làm có hiệ u l ự c các ràng bu ộ c khoá 69 2.1.4 Mô tả các ràng bu ộ c khoá ngoài 71 2.1.5 Duy trì toàn vẹ n tham chi ế u 72 2.1.6 Làm chậ m vi ệ c ki ể m tra ràng bu ộ c. 75 2.2 CÁC RÀNG BUỘỘỘ C TRÊN CÁC THU C TÍNH VÀ CÁC B 77 2.2.1 Các ràng buộ c Not-Null 77 2.2.2 Các ràng buộ c ki ể m tra (CHECK) d ự a trên thu ộ c tính 78 2.2.3 Các ràng buộ c ki ể m tra (CHECK)d ự a trên b ộ giá tr ị . 80 2.3 SỬỔỘ A Đ I CÁC RÀNG BU C 81 2.3.1 Đặ t tên cho các ràng bu ộ c 81 2.3.2 Sử a đ ổ i các ràng bu ộ c trên các b ả ng 82 2.4 CÁC RÀNG BUỘ C M Ứ C L ƯỢ C Đ Ồ VÀ CÁC TRIGGER 83 2.4.1 Các khẳ ng đ ị nh (assertion) 83 So sánh các ràng buộ c 86 2.4.2 Trigger 87 2.5 TỔ NG K Ế T CH ƯƠ NG II 94 MỘỐẬ T S BÀI T P 95 CHƯƠ NG III: L Ậ P TRÌNH 98 3.1 SQL TRONG MÔI TRƯỜ NG L Ậ P TRÌNH 98 3.1.1 Vấ n đ ề tr ở ng ạ i không phù h ợ p 99 3.1.2 Giao diệ n ngôn ng ữ ch ủ /SQL 100 3.1.3 Phầ n khai báo (DECLARE) 101 3.1.4 Sử d ụ ng các bi ế n dùng chung. 102 3.1.5 Các câu lệ nh Select đ ơ n hàng 104 5
3.1.6 Con trỏ 105 3.1.7 Cậ p nh ậ t b ằ ng con tr ỏ 109 3.1.8 Bả o v ệ kh ỏ i s ự c ậ p nh ậ t đ ồ ng th ờ i 110 3.1.9 Con trỏ cu ộ n (Scrolling Cursor) 112 3.1.10 SQL độ ng 113 3.2 CÁC THỦ T Ụ C Đ ƯỢ C L Ư U GI Ữ (stored procedure) 115 3.2.1 Tạ o các hàm và các th ủ t ụ c PSM 116 3.2.2 Mộ t vài d ạ ng câu l ệ nh đ ơ n gi ả n trong PSM 118 3.2.3 Các câu lệ nh r ẽ nhánh. 119 3.2.4 Các truy vấ n trong PSM 121 3.2.5 Vòng lặ p trong PSM 123 3.2.6 Vòng lặ p for 125 3.2.7 Nhữ ng câu l ệ nh l ặ p khác 126 3.3 MÔI TRƯỜ NG SQL 130 3.3.1 Môi trườ ng 131 3.3.2 Lượ c đ ồ 132 3.3.4 Client và Server trong môi trườ ng SQL 135 3.3.5 Kế t n ố i 135 3.3.6 Phiên (Session) 136 3.3.7 Modules 137 3.4 SỬỤỆỨỌ D NG GIAO DI N M C G I (call-level interface) 138 3.4.1 Nhậ p môn SQL/CLI 138 3.4.2 Xử lý các l ệ nh 141 3.4.3 Lấ y d ữ li ệ u ra t ừ k ế t qu ả truy v ấ n 143 3.5 GIAO TÁC TRONG SQL 145 3.5.1 Xế p hàng theo th ứ t ự 146 3.5.2 Atomicity 148 3.5.3 Giao tác (Transaction) 150 3.5.4 Read-Only Transaction 152 3.5.5 Dirty Read 153 3.5.6 Các mứ c cô l ậ p khác 156 3.6 AN TOÀN VÀ CẤỀ P QUY N TRONG SQL 158 3.6.1 Các quyề n 158 3.6.2 Tạ o các quy ề n 160 3.6.3 Tiế n trình ki ể m tra đ ặ c quy ề n 161 3.6.4 Cấ p các quy ề n 163 3.6.5 Biể u đ ồ grant 165 3.6.6 Hủ y b ỏ các quy ề n 165 3.7 TỔ NG K Ế T CH ƯƠ NG III 171 6
CHƯƠ NG I: SQL CƠẢ B N Giố ng nh ư các ngôn ng ữ b ậ c cao khác, ngôn ng ữ SQL đ ượ c xây d ự ng dự a trên các ch ữ cái, các ch ữ s ố , các ký t ự (d ấ u phép toán, d ấ u ngăn, d ấ u cách và các ký tự đ ặ c bi ệ t) và m ộ t t ậ p các t ừ khóa. M ộ t l ệ nh c ủ a SQL có thểượế đ c vi t trên m ộ t dòng ho ặề c nhi u dòng, k ế t thúc b ằấấ ng d u ch m phả y “;”. Ngôn ngữ SQL đ ượ c chia thành ba nhóm: - Ngôn ngữị đ nh nghĩa d ữệ li u dùng đ ểảấ mô t c u trúc c ủơởữ a c s d liệ u (các b ả ng, các khung nhìn, các thu ộ c tính, các ch ỉ m ụ c, ) - Ngôn ngữ thao tác d ữ li ệ u cho phép th ự c hi ệ n các thao tác trên c ơ s ở dữệ li u nh ưậ c p nh ậơởữệ t c s d li u và truy v ấấ n l y ra các thông tin từ c ơ s ở d ữ li ệ u. - Ngôn ngữ ki ể m soát d ữ li ệ u bao g ồ m các l ệ nh dùng đ ể qu ả n lý các giao tác, các quyề n truy c ậ p d ữ li ệ u, k ế t n ố i v ớ i server Ngôn ngữ SQL có th ể s ử d ụ ng theo hai ki ể u: ki ể u tr ự c ti ế p và l ậ p trình. SQL trựế c ti p cho phép th ựệộ c hi n m t truy v ấ n và nh ậượếả n đ c k t qu ngay tứ c kh ắ c. SQL l ậ p trình cho phép s ử d ụ ng SQL trong m ộ t ch ươ ng trình viế t b ằ ng ngôn ng ữ ngôn ng ữ l ậ p trình b ậ c cao khác (C, Pascal, ), hoặ c vi ế t các ch ươ ng trình con. Trong chươ ng này chúng ta s ẽ làm quen v ớ i các l ệ nh c ơ b ả n c ủ a SQL. Các lệ nh này đ ượ c minh h ọ a d ự a trên m ộ t c ơ s ở d ữ li ệ u “CÔNGTY” cho ở ph ầ n PH Ụ L Ụ C c ủ a giáo trình. 1.1 CÁC TRUY VẤƠẢ N Đ N GI N TRONG SQL. Giả s ử chúng ta mu ố n đ ư a ra các nhân viên c ủ a đ ơ n v ị có Mãs ố ĐV = 5, chúng ta viế t trong SQL nh ư sau SELECT * FROM NHÂNVIÊN WHERE Mãsố ĐV = 5 ; Truy vấ n này trình bày d ạ ng đ ặ c tr ư ng select-from-where củ a h ầ u h ế t các truy vấ n SQL. • Mệ nh đ ề FROM cho quan h ệ ho ặ c các quan h ệ mà truy v ấ n tham chiế u đ ế n. Trong ví d ụ trên, quan h ệ đó là NHÂNVIÊN. 8
• Mệ nh đ ề WHERE là m ộ t đi ề u ki ệ n, gi ố ng nh ư đi ề u ki ệ n ch ọ n trong đạ i s ố quan h ệ . Các b ộ ph ả i tho ả mãn đi ề u ki ệ n ch ọ n đ ể phù hợ p v ớ i truy v ấ n. Đi ề u ki ệ n ở đây là thu ộ c tính Mãs ố ĐV c ủ a bộả ph i có giá tr ịấảộứềệ 5. T t c các b đáp ng đi u ki n đó s ẽả tho mãn điề u ki ệ n ch ọ n. • Mệ nh đ ề SELECT nói các thu ộ c tính nào c ủ a các b ộ đáp ứ ng đi ề u kiệẽượư n s đ c đ a ra nh ưộầủ m t ph n c a câu tr ảờấ l i. D u * trong ví dụ này ch ỉằấả ra r ng t t c các thu ộ c tính c ủộẽượư a b s đ c đ a ra. Kếảủ t qu c a truy v ấ n là m ộ t quan h ệứấả ch a t t c các b ộế do ti n trình này sả n xu ấ t ra. Mộ t cách gi ả i thích truy v ấ n này là xem xét t ừ ng b ộ giá tr ị c ủ a quan h ệ đượ c k ể ra trong m ệ nh đ ề FROM. Đi ề u ki ệ n trong m ệ nh đ ề WHERE đượ c áp d ụ ng cho b ộ . Chính xác h ơ n, các thu ộ c tính đ ượ c k ể ra trong mệ nh đ ề WHERE đ ượ c thay th ế b ằ ng các giá tr ị c ủ a thu ộ c tính đó ở trong bộ . Sau đó, đi ề u ki ệ n đ ượ c tính, và n ế u đúng thì các thành ph ầ n xu ấ t hi ệ n trong mệề nh đ SELECT đ ượảấ c s n xu t ra nh ưộộủ là m t b c a câu tr ảờ l i. 1.1.1 Phép chiế u trong SQL a) Chúng ta có thể chi ế u m ộ t quan h ệ do m ộ t truy v ấ n SQL s ả n xu ấ t ra lên trên mộ t s ố thu ộ c tính c ủ a nó. Đ ể làm đi ề u đó, ở v ị trí c ủ a d ấ u * trong mệề nh đ SELECT ta li ệ t kê ra m ộốộ t s thu c tính c ủ a quan h ệượỉ đ c ch ra trong mệề nh đ FROM. K ếảẽượế t qu s đ c chi u lên các thu ộ c tính đ ượ c liệ t kê. Ví dụ 1: Đư a ra H ọ đ ệ m và Tên c ủ a các nhân viên ở đ ơ n v ị có mã s ố bằ ng 5. Chúng ta có th ể vi ế t: SELECT Họ đ ệ m, Tên FROM NHÂNVIÊN WHERE Mãsố ĐV =5; Kế t qu ả là m ộ t b ả ng có hai c ộ t, có tên là H ọ đ ệ m và Tên. Các b ộ c ủ a bả ng này là các c ặ p, m ỗ i c ặ p g ồ m H ọ đ ệ m và Tên c ủ a nhân viên, đó là các nhân viên củ a đ ơ n v ị có mã s ố b ằ ng 5. B ả ng k ế t qu ả có d ạ ng nh ư sau: 9
Họ đ ệ m Tên Lê Vân Trầ n Đ ứ c Nam Nguyễ n S ơ n Vũ Hươ ng Giang b) Đôi khi chúng ta muố n t ạ o ra m ộ t quan h ệ v ớ i đ ầ u c ộ t khác v ớ i các thuộ c tính c ủ a quan h ệ đ ượ c k ể ra trong m ệ nh đ ề FROM. Chúng ta có th ể viế t sau tên c ủ a thu ộ c tính m ộ t t ừ khoá AS và m ộ t bí danh (alias), bí danh đó sẽ tr ở thành đ ầ u c ộ t c ủ a quan h ệ k ế t qu ả . T ừ khoá AS là tuỳ ch ọ n, nghĩa là có thể vi ế t bí danh đi ngay sau tên thu ộ c tính mà không c ầ n ph ả i có từ khoá AS. Ví dụ 2: Ta có thể s ử a đ ổ i ví d ụ 1 ở trên đ ể đ ư a ra m ộ t quan h ệ có các thuộ c tính H ọ nhânviên và Tênnhânviên thay cho v ị trí c ủ a H ọ đ ệ m và Tên như sau: SELECT Họ đ ệ m AS H ọ nhânviên, Tên AS Tênnhânviên FROM NHÂNVIÊN WHERE Mãsố ĐV = 5 ; Bả ng k ế t qu ả có d ạ ng nh ư sau: Họ nhânviên Tên nhânviên Lê Vân Trầ n Đ ứ c Nam Nguyễ n S ơ n Vũ Hươ ng Giang c) Mộ t tuỳ ch ọ n khác trong m ệ nh đ ề SELECT s ử d ụ ng m ộ t bi ể u th ứ c ở vị trí c ủ a m ộ t thu ộ c tính. Ví dụ 3: Chúng ta muố n đ ư a ra H ọ đ ệ m, Tên và l ươ ng sau khi đã đ ượ c tăng 10% củ a các nhân viên ở đ ơ n v ị có mã s ố b ằ ng 5. Ta vi ế t: SELECT Họ đ ệ m, Tên, L ươ ng*1.1 AS L ươ ngm ớ i FROM NHÂNVIÊN WHERE Mãsố ĐV =5; 10
Kế t qu ả Họ đ ệ m Tên Lươ ngm ớ i Lê Vân 3300 Trầ n Đ ứ c Nam 4400 Nguyễ n S ơ n 4180 Vũ Hươ ng Giang 2750 d) Chúng ta cũng có thể cho phép m ộ t h ằ ng nh ư là m ộ t bi ể u th ứ c trong mệ nh đ ề SELECT. Ví dụ 4: Ta muố n đ ư a thêm t ừ ‘ngàn đ ồ ng’ vào sau giá tr ị c ủ a l ươ ng, ta viế t: SELECT Họ đ ệ m, Tên, L ươ ng*1.1 AS L ươ ngm ớ i, ‘ngàn đ ồ ng’ AS Đ ơ nv ị tính FROM NHÂNVIÊN WHERE Mãsố ĐV =5; Kế t qu ả Họ đ ệ m Tên Lươ ngm ớ i Đơ nv ị tính Lê Vân 3300 ngàn đồ ng Trầ n Đ ứ c Nam 4400 ngàn đ ồ ng Nguyễ n S ơ n 4180 ngàn đ ồ ng Vũ Hươ ng Giang 2750 ngàn đ ồ ng Chúng ta đã sắ p x ế p m ộ t c ộ t có tên là Đ ơ nv ị tính và m ỗ i b ộ trong câu tr ả lờ i s ẽ có h ằ ng ‘ngàn đ ồ ng’ ở c ộ t th ứ t ư . 1.1.2 Phép chọ n trong SQL Phép toán chọ n c ủ a đ ạ i s ố quan h ệ và nhi ề u th ứ n ữ a s ẵ n có trong m ệ nh đề WHERE c ủ a SQL. Các bi ể u th ứ c đi sau WHERE bao g ồ m các bi ể u thứềệốưểứềệ c đi u ki n gi ng nh các bi u th c đi u ki n trong các ngôn ng ữậ l p trình. Chúng ta có thể xây d ự ng các đi ề u ki ệ n b ằ ng cách so sánh các giá tr ị sử d ụ ng sáu phép toán so sánh =, , =. Các giá tr ị có th ể đ ượ c so sánh bao gồ m các h ằ ng và các thu ộ c tính c ủ a các quan h ệ đ ượ c k ể ra sau FROM. Chúng ta cũng có thể áp d ụ ng các phép toán s ố h ọ c thông thườ ng nh ư +, -, *, / đ ố i v ớ i các giá tr ị s ố tr ướ c khi chúng ta so sánh chúng và áp dụ ng phép n ố i || đ ố i v ớ i các xâu. M ộ t ví d ụ v ề phép so sánh là Mãsố ĐV = 5 11
Ở trong các ví d ụ ở trên. Thu ộ c tính Mãs ố ĐV đ ượ c ki ể m tra xem có bằ ng h ằ ng 5 hay không. H ằ ng này là m ộ t giá tr ị s ố . Các h ằ ng s ố , nh ư các số nguyên và s ốựượửụ th c đ c s d ng và đ ượ c ghi nh ư cách thông th ườ ng trong các ngôn ngữ l ậ p trình. Ngoài các h ằ ng s ố còn có các h ằ ng xâu. Các xâu trong SQL đượ c ghi b ằ ng cách đ ặ t chúng và trong c ặ p d ấ u nháy đ ơ n, ví dụ , ‘Hà n ộ i’. Kế t qu ả c ủ a m ộ t phép so sánh là m ộ t giá tr ị lô gic TRUE ho ặ c FALSE. Các giá trị lô gic có th ể đ ượ c k ế t h ợ p b ằ ng các phép toán logic AND, OR, NOT vớ i các ý nghĩa c ủ a chúng. Ví dụ 5: Truy vấ n sau đây h ỏ i v ề H ọ đ ệ m, Tên và Gi ớ itính c ủ a các nhân viên ở đ ơ n v ị có mã s ố b ằ ng 5 và Gi ớ itính = ‘Nam’ SELECT Họ đ ệ m, Tên, Gi ớ itính FROM NHÂNVIÊN WHERE (Mãsố ĐV =5) AND (Gi ớ itính = ‘Nam’); Kế t qu ả Họ đ ệ m Tên Giớ itính Lê Vân Nam Trầ n Đ ứ c Nam Nam Nguyễ n S ơ n Nam Trong điề u ki ệ n này, chúng ta có AND c ủ a hai giá tr ị logic. Các giá tr ị đó là các phép so sánh bình thườ ng. Ti ế p theo, ta xét ví d ụ sau: SELECT Họ đ ệ m, Tên FROM NHÂNVIÊN WHERE (Mãsố ĐV =5) AND (Gi ớ itính = ‘N ữ ’ OR L ươ ng <= 3000); Họ đ ệ m Tên Lê Vân Vũ Hươ ng Giang Truy vấ n này đòi h ỏ i các nhân viên ho ặ c là n ữ ho ặ c có l ươ ng nh ỏ h ơ n hoặ c b ằ ng 3000. Chú ý r ằ ng các phép so sánh có th ể nhóm l ạ i b ằ ng vi ệ c sửụ d ng các d ấ u ngo ặơ c đ n. Các d ấ u ngo ặầếở c là c n thi t b i vì th ứựư t u tiên củ a các phép toán lô gic trong SQL là gi ố ng nh ư trong các ngôn ng ữ 12
lậ p trình, AND có th ứ t ự cao h ơ n OR, NOT có th ứ t ự cao h ơ n c ả AND và OR. 1.1.3 So sánh các xâu Hai xâu là bằ ng nhau n ế u chúng là cùng m ộ t dãy ký t ự . SQL cho phép các mô tả các ki ể u xâu khác nhau, ví d ụ , các m ả ng ký t ự có đ ộ dài c ố đ ị nh và các danh sách ký tự có đ ộ dài thay đ ổ i. Khi chúng ta so sánh các xâu bằ ng m ộ t trong các phép toán “nh ỏ h ơ n” nh ư là =, chúng ta đang h ỏ i xem có ph ả i xâu này đi tr ướ c xâu kia trong thứ t ự t ừ đi ể n. Nh ư v ậ y, n ế u a1a2 an và b1b2 bm là hai xâu, thì xâu thứ nhấ t là “nh ỏ h ơ n” xâu th ứ hai n ế u ho ặ c a1 < b1, hoặ c n ế u a1 = b1 và a2 < b2, hoặ c a1 = b1, a2 = b2 và a3 < b3 Ta cũng nói rằ ng a1a2 an < b1b2 bm nế u n<m và a1a2 an = b1b2 bn, nghĩa là xâu thứ nh ấ t là m ộ t ti ề n t ố đúng củ a xâu th ứ hai. Ví d ụ ‘na’ < ‘nam’. SQL cũng cung cấ p kh ả năng đ ể so sánh các xâu trên c ơ s ở m ộ t m ẫ u đ ố i chiế u đ ơ n gi ả n. M ộ t d ạ ng l ự a chon c ủ a bi ể u th ứ c logic là s LIKE p trong đó s là mộ t xâu và p là m ộ t m ẫ u đ ố i chi ế u. M ộ t m ẫ u đ ố i chi ế u là mộ t xâu có s ử d ụ ng hai ký t ự đ ặ c bi ệ t % và _. Các ký t ự thông th ườ ng trong p chỉ đ ố i sánh đ ượ c v ớ i chính chúng ở trong s, nh ư ng % có th ể đ ố i sánh vớ i m ộ t dãy có 0 ho ặ c nhi ề u h ơ n các ký t ự trong s, và _ đ ố i sánh v ớ i bấ t kỳ ký t ự nào trong s. Giá tr ị c ủ a bi ể u th ứ c này là đúng khi và ch ỉ khi xâu s hợ p v ớ i m ẫ u p. M ộ t cách t ươ ng t ự , s NOT LIKE p là đúng khi và ch ỉ khi xâu s không hợ p v ớ i m ẫ u p. Ví dụ 6: SELECT Tên FROM NHÂNVIÊN WHERE Tên LIKE ‘N_ _’ ; Truy vấ n này đòi h ỏ i thu ộ c tính Tên có giá tr ị g ồ m 3 ký t ự , ký t ự đ ầ u tiên là N và sau đó là mộ t dãy nào đó g ồ m hai ký t ự . K ế t qu ả c ủ a truy v ấ n này là mộ t t ậ p các tên nhân viên thích h ợ p, ch ẳ ng h ạ n nh ư Nam, Núi, 13
Ví dụ 7: Chúng ta hãy tìm tên củ a các nhân viên có ch ứ a ch ữ a. Ta có truy vấ n sau: SELECT Tên FROM NHÂNVIÊN WHERE Tên LIKE ‘%a%’; Kế t qu ả c ủ a truy v ấ n này là m ộ t t ậ p các tên nhân viên tho ả mãn đi ề u kiệ n ch ọ n, ch ẳ ng h ạ n nh ư Nam, Thanh, Hoa. 1.1.4 Ngày tháng và thờ i gian Các thể hi ệ n c ủ a SQL nói chung h ỗ tr ợ ngày tháng và th ờ i gian nh ư nhữểữệặệ ng ki u d li u đ c bi t. Các giá tr ị này th ườ ng trình bày đ ượ c trong nhiề u d ạ ng khác nhau nh ư 14/5/1948 ho ặ c 14-05-48. Ở đây chúng ta s ẽ chỉ mô t ả cách ghi chu ẩ n c ủ a SQL. Mộ t h ằ ng ngày tháng đ ượ c bi ể u di ễ n b ằ ng t ừ khoá DATE sau đó là m ộ t xâu có dạ ng đ ặ c bi ệ t đ ể bên trong c ặ p d ấ u nháy đ ơ n. Ví d ụ : DATE’1948- 05-14’. Bố n ký t ự đ ầ u là các ch ữ s ố bi ể u di ễ n năm, sau đó là d ấ u -, hai ký tự ti ế p theo là các ch ữ s ố bi ể u di ễ n tháng, ti ế p theo là d ấ u - và cu ố i cùng là hai ký tự s ố bi ể u di ễ n ngày. Mộằờ t h ng th i gian đ ượểễươựằừ c bi u di n t ng t b ng t khoá TIME và m ộ t xâu đượ c đ ặ t trong c ặ p d ấ u nháy đ ơ n. Xâu này có hai ch ữ s ố cho gi ờ trên đồ ng h ồ quân s ự (24 gi ờ ), sau đó là d ấ u hai ch ấ m, hai ch ữ s ố cho phút, mộ t d ấ u hai ch ấ m n ữ a và hai ch ữ s ố cho giây. N ế u ph ầ n l ẻ c ủ a giây là cầế n thi t, chúng ta có th ểếụớộấấ ti p t c v i m t d u ch m và m ộố t s các ch ữ số có nghĩa. Ví d ụ , TIME’15:00:02.5’ bi ể u di ễ n th ờ i gian 15 gi ờ không phút hai giây 5 phầ n m ườ i. Th ờ i gian còn có th ể đ ượ c bi ể u di ễ n theo nhiề u cách khác n ữ a. Để k ế t h ợ p ngày tháng và th ờ i gian chúng ta s ử d ụ ng m ộ t giá tr ị ki ể u TIMESTAMP. Các giá trị này g ồ m m ộ t t ừ khoá TIMESTAMP, m ộ t giá tr ị ngày tháng, mộ t kho ả ng cách và m ộ t giá tr ị th ờ i gian. Ví d ụ , TIMESTAMP’1948-05-14 12:00:00’ biể u di ễ n 12 gi ờ tr ư a ngày 14 tháng 5 năm 1948. 14
Chúng ta có thể so sánh ngày tháng và th ờ i gian b ằ ng cách s ử d ụ ng các phép toán so sánh giố ng nh ư đ ố i v ớ i các s ố ho ặ c các xâu. Nh ư v ậ y, d ấ u , k ế t qu ả phép so sánh là UNKNOWN. Giá trị UNKNOWN là m ộ t giá tr ị lô gic khác, giố ng nh ư TRUE và FALSE. Chúng ta phả i nh ớ r ằ ng m ặ c dù NULL là m ộ t giá tr ị có th ể xu ấ t hi ệ n trong các bộ nh ư ng nó không ph ả i là m ộ t h ằ ng. Nh ư v ậ y, trong khi các quy tắ c ở trên áp d ụ ng khi chúng ta c ố g ắ ng làm phép toán trên m ộ t bi ể u thứ c mà giá tr ị c ủ a nó là NULL, chúng ta không th ể dùng NULL m ộ t cách rõ như là m ộ t toán h ạ ng. Ví dụ 8: Giả s ử x có giá tr ị null. Khi đó giá tr ị c ủ a x+3 cũng là null. Tuy nhiên null+3 không phả i là m ộ t bi ể u th ứ c SQL h ợ p l ệ . T ươ ng t ự , giá tr ị củ a x = 3 là UNKNOWN b ở i vì chúng ta không th ể nói r ằ ng giá tr ị c ủ a x (mộ t giá tr ị NULL) là b ằ ng 3. Tuy nhiên, phép so sánh NULL = 3 không phả i là phép so sánh SQL đúng. Cách đúng đắ n đ ể h ỏ i xem x có giá tr ị null hay không là dùng bi ể u th ứ c x IS NULL. Biể u th ứ c này có giá tr ị TRUE n ế u x có giá tr ị NULL và nó có 15
giá trị FALSE trong tr ườợượạộ ng h p ng c l i. M t cách t ươự ng t , x IS NOT NULL có giá trị TRUE tr ừ khi giá tr ị c ủ a x là NULL. Trong m ộ t s ố phiên bả n c ủ a SQL, tr ướ c khi th ự c hi ệ n các phép toán v ớ i các giá tr ị null, ng ườ i ta sử d ụ ng các hàm chuy ể n đ ổ i giá tr ị null thành ra giá tr ị 0 (n ế u toán h ạ ng tươứ ng ng có ki ểốặ u s ) ho c thành m ộ t xâu r ỗ ng ‘ ’ n ế u toán h ạươ ng t ng ứng là ki ể u ký t ự . 1.1.6 Giá trị lôgic UNKNOWN Ở trên, chúng ta gi ả thi ế t r ằ ng k ế t qu ả c ủ a m ộ t phép so sánh ho ặ c là TRUE hoặ c là FALSE, và các giá tr ị lôgic này đ ượ c k ế t h ợ p m ộ t cách rõ ràng bằ ng vi ệ c s ử d ụ ng các phép toán AND, OR, NOT. Khi xu ấ t hi ệ n giá trị NULL, các phép so sánh có th ể cho m ộ t giá tr ị lô gic th ứ ba UNKNOWN. Bây giờ chúng ta ph ả i bi ế t các phép toán logic đ ố i x ử nh ư thế nào trên các t ổ h ợ p c ủ a ba giá tr ị logic này. Chúng ta có th ể nghĩ đ ế n TRUE như là 1, FALSE nh ư là 0, UNKNOWN nh ư là 1/2. Khi đó: 1.AND củ a hai giá tr ị lôgic là min c ủ a các giá tr ị đó. Nh ư v ậ y, x AND y là FALSE nế u x ho ặ c y là FALSE, là UNKNOWN n ế u x và y không là FALSE như ng ít nh ấ t có m ộ t giá tr ị là UNKNOWN và là TRUE khi c ả x và y là TRUE. 2.OR củ a hai giá tr ị lôgic là max c ủ a các giá tr ị đó. Nh ư v ậ y x OR y là TRUE nế u x ho ặ c y là TRUE, là UNKNOWN n ế u x và y không là TRUE như ng có ít nh ấ t là m ộ t giá tr ị UNKNOWN và có giá tr ị FALSE n ế u c ả x và y đề u FALSE. 3. Phủ đ ị nh c ủ a giá tr ị lôgic v là 1-v. Nh ư v ậ y, NOT x có giá tr ị TRUE khi x là FALSE, có giá trị FALSE khi x là TRUE và có giá tr ị UNKNOWN khi x là UNKNOWN. Bả ng d ướ i đây t ổ ng k ế t các phép toán lôgic trên các giá trị lôgic: x y x AND y x OR y NOT x TRUE TRUE TRUE TRUE FALSE TRUE UNKNOWN UNKNOWN TRUE FALSE TRUE FALSE FALSE TRUE FALSE UNKNOWN TRUE UNKNOWN TRUE UNKNOWN 16
UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN FALSE FALSE UNKNOWN UNKNOWN FALSE TRUE FALSE TRUE TRUE FALSE UNKNOWN FALSE UNKNOWN TRUE FALSE FALSE FALSE FALSE TRUE Hình 1: Bả ng chân tr ị cho logic ba giá tr ị Các điề u ki ệ n SQL nh ư xu ấ t hi ệ n trong các m ệ nh đ ề WHERE c ủ a các lệ nh select-from-where, áp dụ ng cho m ỗ i b ộ trong m ộ t quan h ệ nào đ ấ y, và vớ i m ỗ i b ộ , m ộ t trong ba giá tr ị TRUE, FALSE, ho ặ c UNKNOWN đượ c sinh ra. Tuy nhiên, ch ỉ có các b ộ mà đ ố i v ớ i nó đi ề u ki ệ n có giá tr ị TRUE sẽ tr ở thành m ộ t ph ầ n c ủ a câu tr ả l ờ i; các b ộ có UNKNOWN ho ặ c FALSE như là giá tr ị s ẽ b ị lo ạ i ra kh ỏ i câu tr ả l ờ i. Ví dụ 9: Giả s ử chúng ta có truy v ấ n trên đây trên quan h ệ NHÂNVIÊN_DỰ ÁN SELECT * FROM NHÂNVIÊN_DỰ ÁN WHERE Số gi ờ 12 ; Mộ t cách tr ự c quan, chúng ta mong đ ợ i nh ậ n đ ượ c m ộ t b ả n sao c ủ a quan hệ NHÂNVIÊN_D Ự ÁN, b ở i vì m ỗ i nhân viên có th ể làm vi ệ c cho m ộ t d ự án ít hơặằ n ho c b ng 12 gi ờặềơ ho c nhi u h n 12 gi ờ . Tuy nhiên, gi ảửằ s r ng có các bộ c ủ a NHÂNVIÊN_D Ự ÁN có giá tr ị NULL trong thành ph ầ n Số gi ờ . Khi đó c ả hai phép so sánh S ố gi ờ 12 đ ượ c tính là UNKNOWN. OR củ a hai UNKNOWN là UNKNOWN. Nh ư v ậ y, v ớ i m ọ i bộ có m ộ t NULL trong thành ph ầ n S ố gi ờ , m ệ nh đ ề WHERE đ ượ c tính là UNKNOWN. Mộộưậ t b nh v y không đ ượảạưộầủ c tr l i nh m t ph n c a câu trả l ờ i cho truy v ấ n. K ế t qu ả đ ượ c đ ư a ra theo ý nghĩa đúng đ ắ n c ủ a truy vấ n là “tìm t ấ t c ả các b ộ NHÂNVIÊN_D Ự ÁN có S ố gi ờ không NULL”. 1.1.7 Sắ p th ứ t ự d ữ li ệ u ra Chúng ta có thể yêu c ầằ u r ng các b ộượộ đ c m t truy v ấạ n t o ra s ẽượ đ c biểễ u di n trong m ộứựắế t th t s p x p. Th ứự t có th ểự d a trên giá tr ịủ c a mộ t thu ộ c tính nào đó, k ế t h ợ p v ớ i giá tr ị c ủ a thu ộ c tính th ứ hai, . Đ ể 17
nhậượữệ n đ c d li u ra theo m ộứựắế t th t s p x p, chúng ta thêm vào l ệ nh select-from-where mộ t m ệ nh đ ề ORDER BY Thứựượầị t đ c ng m đ nh là tăng d ầư n nh ng chúng ta có th ểậữệ nh n d li u ra theo thứ t ự gi ả m d ầ n b ằ ng cách thêm vào t ừ khoá DESC. T ươ ng t ự , chúng ta có thể ch ỉ ra th ứ t ự tăng d ầ n b ằ ng cách thêm vào t ừ khoá ASC (tùy chọ n). Ví dụ 10: Để nh ậ n đ ượ c các H ọ đêm, Tên theo th ứ t ự tăng d ầ n c ủ a Tên củ a t ấ t c ả các nhân viên trong đ ơ n v ị có mã s ố b ằ ng 5, ta có truy v ấ n sau: SELECT Họ đ ệ m, Tên FROM NHÂNVIÊN WHERE Mãsố ĐV = 5 ORDER BY Tên ; 1.1.8 Các hàm thông dụ ng trong SQL Trong SQL có mộ t s ố các hàm xây d ự ng s ẵ n. Sau đây là m ộ t s ố hàm thông dụ ng. 1) Các hàm nhóm: Hàm AVG trả v ề giá tr ị trung bình c ủ a c ộ t. Ví d ụ : SELECT AVG(Lươ ng) FROM NHÂNVIÊN; Hàm MIN trả v ề giá tr ị nh ỏ nh ấ t c ủ a c ộ t. Ví d ụ : SELECT MIN(Lươ ng) FROM NHÂNVIÊN ; Hàm MAX trả v ề giá tr ị l ớ n nh ấ t c ủ a c ộ t. Ví d ụ : SELECT MAX(Lươ ng) FROM NHÂNVIÊN ; Hàm SUM trả v ề t ổ ng các giá tr ị c ủ a c ộ t. Ví d ụ : SELECT SUM(Lươ ng) FROM NHÂNVIÊN ; Hàm COUNT trả v ề s ố l ượ ng các b ả n ghi. Ví d ụ : 18
SELECT COUNT(*) FROM NHÂNVIÊN ; Việ c s ử d ụ ng các hàm này trong các phép toán nhóm s ẽ nói đ ế n trong các phầ n sau. 2) Các hàm xử lý các chu ỗ i ký t ự Hàm ASCI I, trả v ề giá tr ị mã ASCII c ủ a ký t ự bên trái chu ỗ i. Ví d ụ : Print ASCII (‘Tôi’); trả v ề k ế t qu ả 84 (mã ASCII c ủ a T). Hàm CHAR, chuyể n đ ổ i mã ASCII sang ký t ự . Ví d ụ : Print CHAR(35) ; trả v ề k ế t qu ả ký t ự # Hàm UPPER, chuyể n đ ổ i chu ỗ i sang ki ể u ch ữ hoa. Ví d ụ : Print UPPER(‘Nam’); trả v ề k ế t qu ả NAM Hàm LOWER, chuyể n đ ổ i chu ỗ i sang ki ể u ch ữ th ườ ng. Ví d ụ : Print LOWER(‘NAM’) ; trả v ề k ế t qu ả nam Hàm LEN, trả v ề đ ộ dài c ủ a chu ỗ i. Ví d ụ : Print LEN(‘NAM’) ; trả v ề k ế t qu ả 3. Hàm LTRIM, loạ i b ỏ các kho ả ng tr ố ng bên trái c ủ a chu ỗ i. Ví d ụ : Print LTRIM(‘ NAM’) ; trả v ề k ế t qu ả ‘NAM’. Hàm RTRIM, loạ i b ỏ các kho ả ng tr ố ng bên ph ả i c ủ a chu ỗ i. Ví d ụ : Print RTRIM(‘NAM ’) ; trả v ề k ế t qu ả ‘NAM’. Hàm LEFT(chuỗ i,n) tr ả v ề n ký t ự bên trái c ủ a chu ỗ i. Ví d ụ Print LEFT(‘NAM’, 2) ; trả v ề k ế t qu ả ‘NA’. Hàm RIGHT(chuỗ i,n) tr ả v ề n ký t ự bên ph ả i c ủ a chu ỗ i. Ví d ụ Print LEFT(‘NAM’, 1) ; trả v ề k ế t qu ả ‘AM’. Hàm CHARINDEX (chuỗ i1, chu ỗ i2) tr ả v ề v ị tr ị b ắ t đ ầ u c ủ a chu ỗ i 1 trong chuỗ i 2. Ví d ụ : CHARINDEX(‘Tâm’,‘Hữ u Tâm’) tr ả v ề k ế t qu ả 4. 3) Các hàm thờ i gian Hàm GETDATE() trả v ề ngày tháng năm c ủ a h ệ th ố ng. Ví dụ SELECT GETDATE() tr ả v ề k ế t qu ả : 2004-10-17 14:25:36.234 19
Hàm DATEPART() trả v ề m ộ t ph ầ n c ủ a m ộ t chu ỗ i d ạ ng ngày tháng đầ y đ ủ DATEPART(d,GETDATE()), trả v ề ngày DATEPART(m,GETDATE()), trả v ề tháng DATEPART(yy,GETDATE()), trả v ề năm .Các tham s ố d,m,yy là đị nh d ạ ng ngày, tháng, năm, Hàm DATEDIFF (đị nh d ạ ng, Ngàytr ướ c, Ngàysau) hi ệ u s ố gi ữ a Ngày sau và Ngàytrướ c Hàm DAY trả v ề ngày, Hàm MONTH tr ả v ề tháng, Hàm YEAR tr ả về năm 4) Các hàm toán họ c Hàm SQUARE trả v ề bình ph ươ ng c ủ a m ộ t bi ể u th ứ c. Hàm SQRT trả v ề căn b ậ c hai c ủ a m ộ t bi ể u th ứ c Hàm ROUND trả v ề s ố làm tròn c ủ a m ộ t bi ể u th ứ c 5) Các hàm chuyể n đ ố i Hàm CAST trả v ề giá tr ị có ki ể u d ữ li ệ u theo đ ị nh nghĩa. Ví d ụ PRINT CAST (GETDATE() AS VARCHAR) trả v ề Oct 18 2004. Hàm CONVERT chuyể n đ ổ i giá tr ị t ừ ki ể u này sang ki ể u khác. 1.2 CÁC TRUY VẤỒỀƠỘ N BAO G M NHI U H N M T QUAN HỆ Sứạủạố c m nh c a đ i s quan h ệả là kh năng t ổợ h p hai ho ặề c nhi u quan hệ thông qua các phép n ố i, tích, h ợ p, giao và tr ừ . Trong SQL có t ấ t c ả các phép toán đó. 1.2.1 Tích và nố i trong SQL SQL có mộ t cách đ ơ n gi ả n đ ể ghép c ặ p các quan h ệ vào m ộ t truy v ấ n: liệ t kê t ừ ng quan h ệ trong m ệ nh đ ề FROM. Sau đó, các m ệ nh đ ề SELECT và WHERE có thể tham chi ế u đ ế n các thu ộ c tính c ủ a b ấ t kỳ quan h ệ nào bên trong mệ nh đ ề FROM. 20
Ví dụ 11: Giả s ử chúng ta mu ố n bi ế t tên c ủ a nhân viên và tên đ ơ n v ị c ủ a ngườ i đó. Đ ể tr ả l ờ i cho câu h ỏ i này, chúng ta c ầ n hai quan h ệ NHÂNVIÊN và ĐƠ NV Ị . Ta có truy v ấ n sau: SELECT Tên, TênĐV FROM NHÂNVIÊN, ĐƠỊ NV WHERE NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV ; Truy vấ n này đòi h ỏ i chúng ta xem xét t ấ t c ả các c ặ p b ộ giá tr ị , m ộ t t ừ ĐƠ NV Ị và b ộ kia t ừ NHÂNVIÊN. Đi ề u ki ệ n trên các c ặ p này đ ượ c nói rõ trong mệ nh đ ề WHERE: Thành ph ầ n Mãs ố ĐV trong các b ộ này ph ả i có giá trị nh ư nhau. Khi nào chúng ta tìm th ấ y m ộ t c ặ p b ộ tho ả mãn đi ề u kiệ n, chúng ta đ ư a ra các thu ộ c tính Tên c ủ a b ộ t ừ quan h ệ NHÂNVIÊN và thuộ c tính TênĐV c ủộừ a b t quan h ệƠỊưộầủ Đ NV nh m t ph n c a câu trả l ờ i. Quá trình này đ ượ c mô t ả b ằ ng hình v ẽ d ướ i đây: Tên Mã số ĐV Mã số ĐV TênĐV Có bằ ng nhau ? ĐƠỊ NV NHÂNVIÊN Nế u đúng thì đ ư a ra N ế u đúng thì đ ư a ra Hình 2: minh hoạ truy v ấ n c ủ a ví d ụ 11. 1.2.2 Làm rõ nghĩa các thuộ c tính Đôi khi chúng ta đòi hỏ i m ộ t truy v ấ n bao g ồ m nhi ề u quan h ệ và trong nhữ ng quan h ệ này có hai ho ặ c nhi ề u thu ộ c tính có cùng tên. N ế u nh ư vậ y, chúng ta c ầ n có cách đ ể ch ỉ rõ thu ộ c tính nào trong s ố các thu ộ c tính đó là đượ c s ử d ụ ng. SQL gi ả i quy ế t v ấ n đ ề này b ằ ng cách cho phép ta đặ t tên quan h ệ và m ộấấởằướộ t d u ch m đ ng tr c thu c tính. Nh ưậ v y, R.A tham chiế u đ ế n thu ộ c tính A c ủ a quan h ệ R. 21
Trong ví dụ 11, hai quan h ệ NHÂNVIÊN và Đ Ơ NV Ị có các thu ộ c tính Mãsố ĐV trùng tên. Đ ể phân bi ệ t, trong m ệ nh đ ề WHERE ta vi ế t NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV Mộ t quan h ệ , theo sau là m ộ t d ấ u ch ấ m đ ượ c cho phép ngay c ả trong trườ ng h ợ p khi không có s ự không rõ nghĩa. Ví d ụ , chúng ta hoàn toàn thoả i mái khi vi ế t truy v ấ n có d ạ ng nh ư sau: SELECT NHÂNVIÊN.Tên, ĐƠỊ NV .TênĐV FROM NHÂNVIÊN, ĐƠỊ NV WHERE NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV ; Kế t qu ả c ủ a truy v ấ n 11 là: Tên TênĐV Vân Nghiêncứ u Nam Nghiêncứ u Thanh Hànhchính Bằ ng Hànhchính Sơ n Nghiênc ứ u Giang Nghiêncứ u Hoa Hànhchính Giáp Lãnhđạ o 1.2.3 Các biế n b ộ Việ c làm rõ nghĩa các thu ộ c tính b ằ ng cách thêm tên quan h ệ vào đ ầ u hoạ t đ ộ ng khi m ộ t truy v ấ n bao hàm t ổ h ợ p nhi ề u quan h ệ khác nhau. Tuy nhiên, đôi khi chúng ta cầ n đòi h ỏ i môt truy v ấ n bao hàm hai ho ặ c nhi ề u bộ t ừ cùng m ộ t quan h ệ Chúng ta có th ể li ệ t kê m ộ t quan h ệ R bao nhiêu lầ n nh ư ta mu ố n trong m ộ t m ệ nh đ ề FROM, nh ư ng chúng ta c ầ n có cách tham chiế u đ ế n t ừ ng l ầ n có m ặ t c ủ a R. SQL cho phép chúng ta đ ị nh nghĩa đố i v ớ i t ừ ng l ầ n có m ặ t c ủ a R trong m ệ nh đ ề FROM m ộ t “bídanh” mà chúng ta sẽ tham chi ếế u đ n nó nh ưộếộỗầửụủ là m t bi n b . M i l n s d ng c a R trong mệ nh đ ề FROM đ ượ c theo sau b ằ ng m ộ t t ừ khoá AS và tên c ủ a biế n b ộ . T ừ khóa AS là không b ắ t bu ộ c, có th ể có ho ặ c không. Trong phạ m vi tài li ệ u này, chúng ta s ẽ b ỏ qua t ừ khoá AS. 22
Trong các mệ nh đ ề SELECT và WHERE, chúng ta có th ể làm rõ nghĩa các thuộ c tính c ủ a R b ằ ng cách thêm vào tr ướ c chúng m ộ t bi ế n b ộ thích hợ p và m ộấấ t d u ch m. Nh ưậộếộụụư v y, m t bi n b ph c v nh là m ộ t tên khác củ a quan h ệ R và có th ể đ ượ c s ử d ụ ng trong v ị trí c ủ a nó khi chúng ta muố n. Ví dụ 13: Giả s ử chúng ta mu ố n đ ư a ra tên c ủ a nhân viên và tên c ủ a ngườ i giám sát nhân viên đó. Nh ư v ậ y, các tên này đ ề u đ ượ c l ấ y t ừ quan hệ NHÂNVIÊN. S ử d ụ ng các bi ế n b ộ nh ư các bí danh cho hai s ử d ụ ng củ a NHÂNVIÊN, chúng ta có th ể vi ế t truy v ấ n nh ư sau: SELECT NV.Tên, NV1.Tên FROM NHÂNVIÊN NV, NHÂNVIÊN NV1 WHERE NV.MãsôNGS = NV1.Mã số NV; Chúng ta nhìn thấ y trong m ệ nh đ ề FROM mô t ả c ủ a hai bi ế n b ộ , NV và NV1; mỗ i bi ế n b ộ là m ộ t bí danh cho quan h ệ NHÂNVIÊN. Các bi ế n b ộ đượ c s ử d ụ ng trong m ệ nh đ ề SELECT đ ể tham chi ế u đ ế n các thành ph ầ n Tên củ a hai b ộ . Các bí danh này cũng đ ượ c s ử d ụ ng trong m ệ nh đ ề WHERE để nói r ằ ng hai b ộ t ừ NHÂNVIÊN đ ượ c bi ể u di ễ n b ở i NV và NV1 có giá trị nh ư nhau trong các thành ph ầ n MãsôNGS và Mãs ố NV c ủ a chúng Kế t qu ả c ủ a truy v ấ n 13 NV.Tên NV1.Tên Vân Nam Nam Giáp Thanh Bằ ng Bằ ng Giáp Sơ n Nam Giang Nam Hoa Bằ ng 1.2.4 Phép hợ p, phép giao, phép tr ừ c ủ a các truy v ấ n Đôi khi chúng ta muố n t ổ h ợ p các quan h ệ b ằ ng cách s ử d ụ ng các phép toán tậ p h ợ p c ủ a đ ạ i s ố quan h ệ : h ợ p, giao, tr ừ . SQL cung c ấ p các phép toán tươứ ng ng áp d ụ ng cho các k ếảủ t qu c a các truy v ấớềệ n v i đi u ki n là 23
các truy vấ n đó t ạ o ra các quan h ệ có cùng danh sách các thu ộ c tính và các kiể u thu ộ c tính. Các t ừ khoá đ ượ c s ử d ụ ng là UNION, INTERSECT và EXCEPT cho hợ p, giao và tr ừ t ươ ng ứ ng. Các t ừ nh ư UNION đ ượ c s ử dụ ng gi ữ a hai truy v ấ n, và các truy v ấ n này ph ả i đ ượ c đ ặ t trong c ặ p d ấ u ngoặ c đ ơ n. Ví dụ 14: Giả s ử chúng ta mu ố n đ ư a ra Mãs ố NV c ủ a các nhân viên làm việ c cho d ự án có Mãs ố DA =1 và các nhân viên làm vi ệ c cho d ự án có Mã số DA = 2. S ử d ụ ng quan h ệ NHÂNVIÊN_D Ự ÁN, ta vi ế t truy v ấ n nh ư sau: (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 1) UNION (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 2) Lệ nh SELECT đ ầ u đ ư a ra các Mãs ố NV c ủ a các nhân viên làm vi ệ c cho dự án có Mãs ố DA =1, l ệ nh SELECT sau đ ư a ra các Mãs ố NV c ủ a các nhân viên làm việ c cho d ự án có Mãs ố DA = 2, hai t ậ p h ợ p này h ợ p v ớ i nhau tạ o thành câu tr ả l ờ i cho truy v ấ n. Kế t qu ả Mãsố NV NV001 NV002 NV016 NV018 (các bộ trùng l ặ p b ị lo ạ i b ỏ ) Ví dụ 15: Theo cách tươ ng t ự , chúng ta mu ố n đ ư a ra Mãs ố NV c ủ a các nhân viên vừ a làm vi ệ c cho d ự án có Mãs ố DA =1 v ừ a làm vi ệ c cho d ự án có Mã số DA = 2. S ử d ụ ng quan h ệ NHÂNVIÊN_D Ự ÁN, ta vi ế t truy v ấ n như sau: (SELECT Mãsố NV 24
FROM NHÂNVIÊN_DỰ ÁN WHERE Mãs ố DA = 1) INTERSECT (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãs ố DA = 2) Kế t qu ả Mãsố NV NV001 NV018 Ví dụ 16: Giả s ử chúng ta mu ố n đ ư a ra Mãs ố NV c ủ a các nhân viên làm việ c cho d ự án có Mãs ố DA =1 nh ư ng không làm vi ệ c cho d ự án có Mã số DA = 2. S ử d ụ ng quan h ệ NHÂNVIÊN_D Ự ÁN, ta vi ế t truy v ấ n nh ư sau: (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 1) EXCEPT (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 2) Kế t qu ả Mãsố NV (không có) 1.3 CÁC TRUY VẤ N CON Trong SQL, mộ t truy v ấ n có th ể đ ượ c s ử d ụ ng trong nhi ề u cách khác nhau để giúp vi ệ c tính giá tr ị c ủ a truy v ấ n khác. M ộ t truy v ấ n là m ộ t ph ầ n củ a truy v ấ n khác đ ượ c g ọ i là m ộ t truy v ấ n con. Các truy v ấ n con l ạ i có thể có các truy v ấ n con và nh ư v ậ y có th ể đi xu ố ng r ấ t nhi ề u m ứ c. Chúng ta đã có cơ h ộ i nhìn th ấ y vi ệ c s ử d ụ ng truy v ấ n con. Trong các ví d ụ ở phầ n trên, chúng ta đã xây d ự ng các truy v ấ n h ợ p, giao, tr ừ b ằ ng cách n ố i hai truy vấ n con đ ể t ạ o nên truy v ấ n đ ầ y đ ủ . Có r ấ t nhi ề u cách đ ể s ử dụ ng các truy v ấ n con: 25
1. Các truy vấ n con có th ể tr ả l ạ i m ộ t h ằ ng đ ơ n và có th ể so sánh h ằ ng đó vớ i giá tr ị khác trong m ệ nh đ ề WHERE. 2. Các truy vấ n con có th ể tr ả l ạ i các quan h ệ và có th ể s ử d ụ ng các quan hệ này b ằ ng nhi ề u cách trong m ệ nh đ ề WHERE. 3. Các truy vấ n con có th ể có các quan h ệ c ủ a chúng xu ấ t hi ệ n trong mệ nh đ ề FROM gi ố ng nh ư các quan h ệ đ ượ c l ư u gi ữ có th ể . 1.3.1 Các truy vấ n con t ạ o ra các giá tr ị vô h ướ ng Mộ t giá tr ị nguyên t ửểấệưộ có th xu t hi n nh m t thành ph ầủộộ n c a m t b đượ c xem là m ộ t vô h ướ ng. M ộ t bi ể u th ứ c select-from-where có thể t ạ o ra mộ t quan h ệ có s ố các thu ộ c tính tuỳ ý và có s ố b ộ giá tr ị tuỳ ý trong quan hệ . Tuy nhiên, thông th ườ ng chúng ta ch ỉ quan tâm đ ế n các giá tr ị c ủ a mộ t thu ộ c tính đ ơ n. H ơ n n ữ a, đôi lúc chúng ta có th ể suy ra t ừ thông tin về khoá ho ặ c t ừ các thông tin khác. Ch ẳ ng h ạ n, chúng ta có th ể so sánh kếảủộ t qu c a m t truy v ấ n con nh ưậớộằ v y v i m t h ng ho ặộộ c m t thu c tính. Ví dụ 17: Chúng ta muố n đ ư a ra H ọ đ ệ m và Tên c ủ a các nhân viên trong đơ n v ị có tên là ‘Nghiênc ứ u’. Chúng ta c ầ n truy v ấ n hai quan h ệ : NHÂNVIÊN và ĐƠ NV Ị . B ở i vì ch ỉ có quan h ệ đ ầ u có thông tin v ề Họ đ ệ m và Tên và ch ỉ có quan h ệ th ứ hai có các tên c ủ a đ ơ n v ị . Thông tin đượ c liên k ế t b ằ ng ‘Mãs ố ĐV’. Có nhiề u cách khác nhau đ ể nhìn vào truy v ấ n này. Chúng ta ch ỉ c ầ n quan hệƠỊểậượốố Đ NV đ nh n đ c s Mãs ĐV cho đ ơị n v có tên là ‘Nghiên c ứ u’. Mỗ i khi chúng ta có nó, chúng ta có th ể truy v ấ n quan h ệ NHÂNVIÊN đ ể tìm ra họ đ ệ m và tên c ủ a các nhân viên trong đ ơ n v ị đó. V ấ n đ ề đ ầ u tiên là nhậ n đ ượ c Mãs ố ĐV. Chúng ta có th ể vi ế t nó nh ư m ộ t truy v ấ n con và k ế t quảủẽộịơ c a nó s là m t giá tr đ n. Có th ểửụ s d ng giá tr ị này trong truy v ấ n “chính” để đ ạ t đ ượ c k ế t qu ả mong mu ố n: 1) SELECT Họ đ ệ m, Tên 2) FROM NHÂNVIÊN 3) WHERE Mãsố ĐV = 4) (SELECT Mãsố ĐV 26
5) FROM ĐƠỊ NV 6) WHERE TênĐV = ‘Nghiêncứ u’ ; Các dòng từ 4) đ ế n 6) là truy v ấ n con. Ch ỉ nhìn vào truy v ấ n con này chúng ta sẽ th ấ y r ằ ng k ế t qu ả s ẽ là m ộ t quan h ệ có m ộ t thu ộ c tính là Mãsố ĐV và chúng ta hy v ọ ng s ẽ tìm th ấ y ch ỉ m ộ t b ộ trong quan h ệ này, giả s ử đó là 5. Khi đã thự c hi ệ n truy v ấ n con này, chúng ta có th ể th ự c hi ệ n các dòng t ừ 1) đế n 3) c ủ a truy v ấ n trên nh ư là giá tr ị 5 đã thay th ế truy v ấ n con. Nh ư vậ y, truy v ấ n “chính” s ẽ đ ượ c th ự c hi ệ n nh ư là SELECT Họ đ ệ m, Tên FROM NHÂNVIÊN WHERE Mãsố ĐV = 5 ; Kế t qu ả c ủ a truy v ấ n là H ọ đ ệ m và Tên c ủ a các nhân viên c ủ a đ ơ n v ị có tên là ‘Nghiên cứ u’. 1.3.2 Các điề u ki ệ n có bao hàm các quan h ệ Có mộ t s ố các phép toán c ủ a SQL mà ta có th ể áp d ụ ng cho m ộ t quan h ệ R và tạộếả o ra m t k t qu lôgic. Thông th ườ ng, quan h ệẽếảủ R s là k t qu c a mộ t truy v ấ n con select-from-where. Mộ t s ố các phép toán (EXISTS, IN, ALL, ANY) sẽượả đ c gi i thích ở đây d ướạ i d ng đ ơả n gi n nh ấủ t c a nó, trong đó có chứ a m ộ t giá tr ị vô h ướ ng s. Trong hoàn c ả nh này, R ph ả i là mộ t quan h ệ có m ộ t c ộ t 1. EXISTS R là mộ t đi ề u ki ệ n có giá tr ị true khi và ch ỉ khi R không r ỗ ng. 2. s IN R có giá trị TRUE khi và ch ỉ khi s b ằ ng m ộ t trong các giá tr ị c ủ a R. Tươ ng t ự , s NOT IN R có giá tr ị TRUE khi và ch ỉ khi s không b ằ ng giá tr ị nào trong R. Ở đây, chúng ta gi ả thi ế t R là quan h ệ có m ộ t c ộ t. 3. s > ALL R là đúng khi và chỉ khi s l ớ n h ơ n m ọ i giá tr ị trong quan h ệ mộ t c ộ t R. T ươ ng t ự , có th ể thay d ấ u > b ằ ng m ộ t trong các phép so sánh khác. 4. s > ANY R là đúng khi và chỉ khi s l ớ n h ơ n ít nh ấ t là m ộ t giá tr ị trong quan hệ m ộ t c ộ t R 27
Các phép toán EXISTS, IN, ALL và ANY có thể đ ượ c ph ủ đ ị nh b ằ ng cách đặ t NOT ở tr ướ c bi ể u th ứ c. Ví d ụ , NOT EXISTS R đúng khi và ch ỉ khi R là rỗ ng. NOT s > ALL R là đúng khi và ch ỉ khi s không ph ả i là giá tr ị max trong R và NOT s > ANY R là đúng khi và chỉ khi s là giá tr ị min trong R. 1.3.3 Các điề u ki ệ n có bao hàm các b ộ Mộ t b ộ trong SQL đ ượ c bi ể u di ễ n b ằ ng m ộ t danh sách các giá tr ị vô hướ ng đ ượ c đ ặ t trong d ấ u ngo ặ c. Ví d ụ : (5,’Nghiênc ứ u’, ‘NV002’, 2000- 09-15) là mộ t b ộ c ủ a quan h ệ Đ Ơ NV Ị . Nế u m ộ t b ộ t có cùng s ố thành ph ầ n nh ư m ộ t quan h ệ R thì có th ể so sánh t và R. Ví dụ : t IN R ho ặ c t<> ANY R (Chú ý r ằ ng khi so sánh m ộ t bộ v ớ i các thành ph ầ n c ủ a m ộ t quan h ệ R, chúng ta ph ả i so sánh các thành phầ n theo th ứ t ự c ủ a các thu ộ c tính c ủ a R). Ví dụ 18: Giả s ử ta mu ố n đ ư a ra H ọ đ ệ m và tên c ủ a các nhân viên có lươ ng cao nh ấ t ở trong t ừ ng đ ơ n v ị . Ta vi ế t truy v ấ n sau: 1) SELECT Họ đ ệ m, Tên 2) FROM NHÂNVIÊN 3) WHERE (Lươ ng, Mãs ố ĐV) IN 4) (SELECT (MAX(Lươ ng), Mã s ố ĐV 5) FROM NHÂNVIÊN 6) GROUP BY Mã số ĐV); Truy vấ n này bao g ồ m m ộ t truy v ấ n chính và m ộ t truy v ấ n con. Truy vấ n con kh ả o sát các b ộ c ủ a quan h ệ NHÂNVIÊN và đ ư a ra m ộ t quan h ệ gồ m các b ộ v ớ i giá tr ị c ủ a các thu ộ c tính MAX(L ươ ng) và Mãs ố ĐV. Truy vấ n chính, t ừ dòng 1) đ ế n dòng 3) xem xét các b ộ c ủ a quan h ệ NHÂNVIÊN để tìm các b ộ có thành ph ầ n L ươ ng và Mãs ố ĐV là m ộ t trong các bộ do truy v ấ n con đ ư a ra. V ớỗộưậọệ i m i b nh v y, H đ m và Tên s ẽ đượ c đ ư a ra và cho chúng ta t ậ p h ợ p nh ữ ng nhân viên có l ươ ng cao nh ấ t trong từ ng đ ơ n v ị nh ư chúng ta mong mu ố n. Kế t qu ả : Họ đ ệ m Tên Trầ n Đ ứ c Nam Phạ m B ằ ng 28
Hoàng Giáp 1.3.4 Các truy vấ n con t ươ ng quan v ớ i nhau Các truy vấ n con đ ơảấ n gi n nh t có th ểượựệộầ đ c th c hi n m t l n cho t ấ t cả , và k ếảượửụ t qu đ c s d ng trong truy v ấứ n m c cao h ơệửụ n. Vi c s d ng các truy vấ n con l ồ ng nhau ph ứ c t ạ p h ơ n đòi h ỏ i truy v ấ n con đ ượ c th ự c hiệềầỗầốớộ n nhi u l n, m i l n đ i v i m t phép gán giá tr ị cho m ộụ t m c nào đó trong truy vấ n con. Giá tr ị gán xu ấ t phát t ừ m ộ t bi ế n b ộ ở bên ngoài truy vấ n con. M ộ t truy v ấ n con ki ểưậượọộ u nh v y đ c g i là m t truy v ấ n con tươ ng quan. Chúng ta b ắ t đ ầ u b ằ ng m ộ t ví d ụ . Ví dụ 19: Chúng ta muố n đ ư a ra H ọ đ ệ m và Tên c ủ a các nhân viên có lươớơươ ng l n h n l ng trung bình c ủơịủọ a đ n v c a h . Chúng ta xem xét l ầ n lượ t các b ộ c ủ a quan h ệ NHÂNVIÊN, v ớ i m ỗ i b ộ nh ư v ậ y, chúng ta đòi hỏ i trong m ộ t truy v ấ n con li ệ u giá tr ị c ủ a L ươ ng có l ớ n h ơ n trung bình lươủơị ng c a đ n v có mã s ốốư gi ng nh giá tr ịủ c a thành ph ầ n Mãs ốủ ĐV c a bộ hay không. Toàn b ộ truy v ấ n đ ượ c vi ế t nh ư sau: 1) SELECT Họ đ ệ m, Tên 2) FROM NHÂNVIÊN NV 3) WHERE (Lươ ng > 4) (SELECT (AVG(Lươ ng) 5) FROM NHÂNVIÊN 6) WHERE Mãsố ĐV = NV.Mãs ố ĐV; Giố ng nh ư v ớ i các truy v ấ n l ồ ng nhau khác, chúng ta hãy b ắ t đ ầ u t ừ truy vấ n con trong cùng, các dòng t ừ 4) đ ế n 6). N ế u NV.Mãs ố ĐV ở dòng 6 đượ c thay b ằ ng m ộ t s ố nguyên nh ư là 5, chúng ta có th ể hi ể u nó hoàn toàn dễ dàng nh ưộ là m t truy v ấỏ n h i trung bình l ươủơị ng c a đ n v có mã s ố là 5. Truy vấ n con hi ệ n t ạ i có khác m ộ t tý. V ấ n đ ề là ở ch ỗ chúng ta không biế t Mãs ố ĐV có giá tr ị nh ư th ế nào. Tuy nhiên, nh ư chúng ta s ắ p x ế p trên các bộ c ủ a truy v ấ n ngoài t ừ dòng 1) đ ế n dòng 3), m ỗ i b ộ cung c ấ p m ộ t giá trị c ủ a Mã s ố ĐV. Sau đó chúng ta th ự c hi ệ n truy v ấ n t ừ dòng 4) đ ế n dòng 6) vớ i giá tr ịủ đó c a Mã s ốểếị ĐV đ quy t đ nh chân tr ịủệề c a m nh đ WHERE trả i trên các dòng t ừ 3) đ ế n 6). Đi ề u ki ệ n c ủ a dòng 3) là đúng nế u có m ộơị t đ n v có trung bình l ươ ng nh ỏơươủộ h n l ng c a b đang xét. 29
Kế t qu ả Họ đ ệ m Tên Trầ n Đ ứ c Nam Nguyễ n S ơ n Phạ m B ằ ng Hoàng Giáp Khi viế t m ộ t truy v ấ n t ươ ng quan, đi ề u quan tr ọ ng là chúng ta ph ả i nh ậ n thứ c đ ượ c quy t ắ c ph ạ m vi đ ố i v ớ i các tên. Nói chung, m ộ t thu ộ c tính trong mộ t truy v ấ n con thu ộ c v ề m ộ t trong các bi ế n b ộ c ủ a m ệ nh đ ề FROM củ a truy v ấ n con đó n ế u m ộ t quan h ệ nào đó c ủ a bi ế n b ộ có thu ộ c tính đó trong lượ c đ ồ c ủ a nó. N ế u không, chúng ta tìm ở truy v ấ n tr ự c ti ế p ngay bên ngoài . Tuy nhiên, chúng ta có thể s ắ p x ế p cho m ộ t thu ộ c tính thu ộ c v ề m ộ t bi ế n bộ khác n ế u chúng ta vi ếướ t tr c nó m ộếộộấấề t bi n b và m t d u ch m. Đi u đó là vì chúng ta đã đư a ra bí danh NV cho quan h ệ NHÂNVIÊN c ủ a truy vấ n ngoài và vì chúng ta tham chi ế u đ ế n NV.Mã s ố ĐV trong dòng 6). Chú ý rằ ng n ế u hai quan h ệ trong các m ệ nh đ ề FROM c ủ a các dòng 2) và 5) là khác nhau, chúng ta không cầ n đ ế n bí danh. Dĩ nhiên, trong truy v ấ n con, chúng ta có thể tham chi ế u tr ự c ti ế p đ ế n các thu ộ c tính c ủ a quan h ệ đ ượ c chỉ ra trong dòng 2). 1.3.5 Các truy vấ n con trong m ệ nh đ ề FROM Mộ t cách dùng khác đ ố i v ớ i các truy v ấ n con là nh ư các quan h ệ trong mộ t m ệ nh đ ề FROM. Trong danh sách FROM, thay cho m ộ t quan h ệ đ ượ c lư u gi ữ , chúng ta có th ể s ử d ụ ng m ộ t truy v ấ n con đ ể trong d ấ u ngo ặ c. Bở i vì chúng ta không có tên cho k ế t qu ả c ủ a m ộ t truy v ấ n con, chúng ta phả i cho nó m ộ t bí danh bi ế n b ộ . Sau đó chúng ta tham chi ế u đ ế n các b ộ trong kế t qu ả c ủ a m ộ t truy v ấ n con nh ư chúng ta s ẽ tham chi ế u đ ế n các bộ trong m ộ t quan h ệ b ấ t kỳ xu ấ t hi ệ n trong danh sách FROM. Ví dụ 20: Chúng ta hãy xem lạ i v ấ n đ ề c ủ a ví d ụ 18, ở đó chúng ta đã viế t m ộ t truy v ấ n tìm H ọ đ ệ m và Tên c ủ a các nhân viên có l ươ ng cao nh ấ t trong các đơ n v ị . Gi ả s ử r ằ ng chúng ta đã có m ộ t quan h ệ ch ứ a Max(lươ ng) và Mãs ố ĐV. Khi đó vi ệ c tìm các H ọ đ ệ m và Tên s ẽ đ ơ n gi ả n hơ n b ằ ng cách tìm trong quan h ệ NHÂNVIÊN. Truy v ấ n nh ư v ậ y có d ạ ng 30
1) SELECT Họ đ ệ m, Tên 2) FROM NHÂNVIÊN, (SELECT MAX(Lươ ng), Mãs ố ĐV) 3) FROM NHÂNVIÊN 4) GROUP BY Mãsố ĐV) NV1 5) WHERE (Lươ ng, Mãs ố ĐV) = NV1.(MAX(L ươ ng),Mãs ố ĐV); Các dòng từ 2) đ ế n 4) là m ệ nh đ ề FROM c ủ a truy v ấ n ngoài. Trong mệ nh đ ề đó, ngoài quan h ệ NHÂNVIÊN nó còn có m ộ t truy v ấ n con. Truy vấ n này đ ư a ra quan h ệ có hai thu ộ c tính là MAX(L ươ ng) và Mãs ố ĐV. Tậ p h ợ p đó đ ượ c gán cho bí danh là NV1 ở dòng 4. Ở dòng 5), các quan hệ NHÂNVIÊN và truy v ấ n con có bí danh NV1 đ ượ c n ố i theo hai đi ề u kiệ n là L ươ ng và Mãs ố ĐV ph ảư i nh nhau. K ếảưượậ t qu là đ a ra đ c t p Họ đ ệ m và Tên gi ố ng nh ư trong ví d ụ 18. 1.3.6 Các biể u th ứ c n ố i c ủ a SQL Chúng ta có thể xây d ự ng các quan h ệ b ằ ng các phép n ố i khác nhau áp dụ ng trên hai quan h ệ . Các cách này g ồ m tích, n ố i t ự nhiên, n ố i têta, và nố i ngoài. K ế t qu ả có th ể là m ộ t truy v ấ n. Vì các bi ể u th ứ c này t ạ o ra các quan hệ nên chúng có th ể đ ượ c s ử d ụ ng nh ư các truy v ấ n con trong m ệ nh đề FROM c ủ a m ộ t bi ể u th ứ c select-from-where. Dạơảấủộểứốố ng đ n gi n nh t c a m t bi u th c n i là n i chéo (cross join). Thu ậ t ngữ này đ ồ ng nghĩa v ớ i tích Đ ề cac ho ặ c tích. Ví d ụ , n ế u chúng ta mu ố n có tích Đề cac c ủ a hai quan h ệ NHÂNVIÊN và Đ Ơ NV Ị . Chúng ta có th ể nói NHÂNVIÊN CROSS JOIN ĐƠỊ NV ; và kếảẽộ t qu s là m t quan h ệ có 13 c ộứấả t, ch a t t c các thu ộ c tính c ủ a NHÂNVIÊN và ĐƠ NV Ị . M ỗ i m ộ t c ặ p g ồ m m ộ t b ộ c ủ a NHÂNVIÊN m ộ t bộủƠỊẽ c a Đ NV s là m ộộủ t b c a quan h ệếả k t qu . Các thuộ c tính trong quan h ệ tích có th ể đ ượ c g ọ i là R.A, trong đó R là mộ t trong hai quan h ệ n ố i và A là m ộ t trong các thu ộ c tính c ủ a nó. N ế u chỉ có m ộ t trong các quan h ệ có thu ộ c tính là A thì có th ể b ỏ R và d ấ u chấ m đi. Trong hoàn c ả nh hi ệ n t ạ i, b ở i vì quan h ệ NHÂNVIÊN và quan hệ Đ Ơ NV Ị có m ộ t thu ộ c tính chung là Mãs ố ĐV, nên ở trong quan h ệ tích 31
cầ n ph ả i phân bi ệ t chúng NHÂNVIÊN.Mãs ố ĐV và Đ Ơ NV Ị .MÃs ố ĐV, các tên khác củ a các thu ộ c tính trong hai quan h ệ là khác nhau nên không cầ n có tên quan h ệ và d ấ u ch ấ m ở tr ướ c. Tuy nhiên, phép toán tích là m ộ t phép toán ít khi đượ c s ử d ụ ng. Phép n ố i têta là thu ậ n ti ệ n h ơ n. Phép n ố i này gồ m t ừ khoá JOIN đ ượ c đ ặ t gi ữ a hai tên quan h ệ R và S, sau chúng là từ khoá ON và m ộ t đi ề u ki ệ n. Ý nghĩa c ủ a JOIN ON là phép tính tích R x S, sau đó là mộ t phép ch ọ n theo đi ề u kiên đi sau ON. Ví dụ 21: Giả s ử chúng ta mu ố n n ố i hai quan h ệ NHÂNVIÊN và Đ Ơ NV Ị vớềệ i đi u ki n là các b ộượố đ c n i là các b ộ tham chi ếế u đ n cùng m ộ t mã sốơịưậ đ n v . Nh v y, các mã s ốơịừả đ n v t c hai quan h ệảư ph i nh nhau. Chúng ta có thể đòi h ỏ i truy v ấ n này là: NHÂNVIÊN JOIN ĐƠ NV Ị ON NHÂNVIÊN.Mãs ố ĐV = Đ Ơ NV Ị .Mãs ố ĐV; Kế t qu ả l ạ i là m ộ t quan h ệ v ớ i 13 c ộ t cùng v ớ i các tên thu ộ c tính nh ư trên. Tuy nhiên, bây giờ m ộ t b ộ t ừ NHÂNVIÊN và m ộ t b ộ t ừ Đ Ơ NV Ị k ế t hợớ p v i nhau đ ểạ t o thành m ộộếảỉ t b k t qu ch khi hai b ộ có mã s ốơị đ n v như nhau. Trong k ếảộộ t qu , m t c t là th ừở a b i vì m ỗộộủếả i m t b c a k t qu sẽ cùng giá tr ị trong c ả hai thành ph ầ n Mãs ố ĐV Nế u chúng ta lo l ắ ng v ớ i s ự ki ệ n là phép n ố i ở trên có m ộ t thành ph ầ n thừ a, chúng ta có th ểửụểứầủưộ s d ng bi u th c đ y đ nh là m t truy v ấ n con trong mệ nh đ ề FROM và s ử d ụ ng m ệ nh đ ề SELECT đ ể lo ạ i b ỏ các thu ộ c tính không mong muố n. Nh ư v ậ y, chúng ta có th ể vi ế t: SELECT FROM NHÂNVIÊN JOIN ĐƠ NV Ị ON NHÂNVIÊN.Mãs ố ĐV = Đ Ơ NV Ị .Mãs ố ĐV; để nh ậ n đ ượ c m ộ t quan h ệ có 12 c ộ t, đó là các b ộ c ủ a quan h ệ NHÂNVIÊN đượ c m ở r ộ ng thêm các b ộ c ủ a Đ Ơ NV Ị 1.3.7 Nố i t ự nhiên (Natural Join) Phép nố i t ự nhiên khác v ớ i phép n ố i têta ở ch ỗ : 1. Điề u ki ệ n n ố i là t ấ t c ả các c ặ p thu ộ c tính t ừ hai quan h ệ có m ộ t tên chung đượ c so sánh b ằ ng và không có đi ề u ki ệ n nào khác. 32
2. Mộộ t thu c tính trong m ỗặộ i c p thu c tính đ ượ c so sánh b ằượế ng đ c chi u ra ngoài. (nghĩa là trong quan hệ k ế t qu ả không có hai c ộ t gi ố ng nhau). Phép nố i t ự nhiên c ủ a SQL ứ ng x ử m ộ t cách chính xác theo cách đó. Các từ khoá NATURAL JOIN xu ấ t hi ệ n gi ữ a các quan h ệ đ ể bi ể u th ị phép nố i. Ví dụ 22: Giả s ử chúng ta mu ố n làm phép n ố i t ự nhiên c ủ a hai quan h ệ ĐƠỊ NV và NHÂNVIÊN. K ếảẽộ t qu s là m t quan h ệượồứ có l c đ ch a thuộ c tính Mãs ố ĐV c ộ ng v ớ i t ấ t c ả các thu ộ c tính xu ấ t hi ệ n trong c ả hai quan hệ . Bi ể u th ứ c NHÂNVIÊN NATURAL JOIN ĐƠỊ NV Mô tả súc tích quan h ệ mong mu ố n. 1.3.8 Nố i ngoài Nố i ngoài là m ộ t cách đ ể làm tăng k ế t qu ả c ủ a m ộ t phép n ố i b ằ ng các b ộ treo, độ n thêm vào các giá tr ị null. Trong SQL, chúng ta có th ể ch ỉ rõ m ộ t nố i ngoài; NULL đ ượ c s ử d ụ ng nh ư là giá tr ị null. Ví dụ 23: Giả s ử chúng ta đ ư a ra H ọ đ ệ m và Tên c ủ a các nhân viên cũng như H ọ đ ệ m và Tên c ủ a nh ữ ng ng ườ i giám sát h ọ . Trên th ự c t ế , không phả i nhân viên nào cũng có ng ườ i giám sát tr ự c ti ế p, vì v ậ y đ ố i v ớ i nh ữ ng ngườ i không có ng ườ i giám sát tr ự c ti ế p ho ặ c thông tin v ề ng ườ i giám sát củọ a h là không xác đ ị nh (null). N ếốểịảữộưậ u mu n hi n th c nh ng b nh v y, ta sử d ụ ng n ố i ngoài SQL xem nố i ngoài chu ẩ n đ ộ n thêm vào các b ộ treo t ừ hai phía c ủ a các đố i s ố c ủ a chúng là n ố i ngoài đ ầ y đ ủ (full outerjoin). Cú pháp nh ư sau: NHÂNVIÊN FULL OUTER JOIN NHÂNVIÊN ON Mãsố NV = Mãs ố NGS; Kế t qu ả c ủ a phép toán này là m ộ t quan h ệ , trong đó có nh ữ ng b ộ đ ượ c độ n vào các giá tr ị NULL do không có giá tr ị n ố i t ươ ng ứ ng. (Chú ý, trong phép nố i bình th ườ ng không có nh ữộưậấả ng b nh v y). T t c các lo ạố i n i ngoài đượ c nói đ ế n trong các phép toán đ ạ i s ố quan h ệ đ ề u có s ẵ n trong SQL. Nế u chúng ta mu ố n m ộ t left- ho ặ c right-outerjoin, ta thêm vào t ừ LEFT hoặ c RIGHT thích h ợ p vào v ị trí c ủ a t ừ FULL. Ví d ụ : 33
NHÂNVIÊN LEFT OUTER JOIN NHÂNVIÊN ON Mãsố NV = Mãs ố NGS; NHÂNVIÊN RIGHT OUTER JOIN NHÂNVIÊN ON Mãsố NV = Mãs ố NGS; Tiế p theo, gi ả s ử ta mu ố n m ộ t n ố i ngoài t ự nhiên thay vì m ộ t n ố i ngoài têta. Khi đó chúng ta sẽ s ử d ụ ng t ừ khóa NATURAL đ ặ t vào tr ượ c t ừ JOIN và bỏ ON đi. Ví dụ 24: Chúng ta hãy xem lạ i ví d ụ 22, ở đó chúng ta mu ố n n ố i hai quan hệ NHÂNVIÊN và Đ Ơ NV Ị v ớ i đi ề u ki ệ n là các thu ộ c tính Mãs ố ĐV c ủ a hai quan hệ là b ằ ng nhau. N ế u chúng ta s ử a đ ổ i ví d ụ này nh ư sau NHÂNVIÊN NATURAL FULL OUTER JOIN ĐƠỊ NV thì chúng ta sẽậượ nh n đ c không ch ỉ là các b ộượạ đ c t o nên t ừ các b ộ tham gia nố i mà còn có thêm các b ộ đ ượ c đ ộ n vào các giá tr ị NULL Từ khoá FULL có th ể đ ượ c thay th ế b ằ ng LEFT ho ặ c RIGHT trong phép nố i ngoài ở trên. 1.4 CÁC PHÉP TOÁN QUAN HỆẦỦ Đ Y Đ Trướ c tiên chúng ta đ ể ý r ằ ng SQL s ử d ụ ng các quan h ệ nh ư là các túi (bag) chứ không ph ả i nh ư t ậ p h ợ p. Đi ề u đó có nghĩa là m ộ t b ộ có th ể xuấ t hi ệ n nhi ề u l ầ n trong m ộ t quan h ệ . 1.4.1 Loạ i b ỏ trùng l ặ p Như đã nói đ ế n ở trên, khái ni ệ m quan h ệ c ủ a SQL khác v ớ i khái ni ệ m quan hệ tr ừ u t ượ ng đ ượ c trình bày trong mô hình quan h ệ . M ộ t quan h ệ là mộậợ t t p h p, không th ể có nhi ềơộả u h n m t b n sao c ủộộ a m t b cho tr ướ c. Khi mộ t truy v ấ n SQL t ạ o m ộ t quan h ệ m ớ i, h ệ th ố ng SQL không lo ạ i b ỏ các trùng lặ p. Nh ư v ậ y, SQL tr ả l ờ i cho m ộ t truy v ấ n có th ể li ệ t kê nhi ề u lầ n cùng m ộ t b ộ . Nhớ l ạ i r ằ ng m ộ t đ ị nh nghĩa cho m ộ t truy v ấ n select-from-where c ủ a SQL là như sau: Chúng ta b ắ t đ ầ u v ớ i tích Đ ề cac c ủ a các quan h ệ đ ượ c tham chiế u đ ế n trong m ệ nh đ ề FROM. M ỗ i b ộ c ủ a tích đ ượ c ki ể m tra bằ ng đi ề u ki ệ n trong m ệ nh đ ề WHERE và nh ữ ng b ộ nào qua đ ượ c ki ể m tra sẽượư đ c đ a cho d ữệ li u ra cho phép chi ế u phù h ợớệề p v i m nh đ SELECT. Phép chiế u này có th ể sinh ra cùng m ộ t b ộ cho k ế t qu ả t ừ nhi ề u 34
bộ khác nhau c ủ a tích, và n ếưậỗả u nh v y, m i b n sao c ủếảẽượ a k t qu s đ c in ra. Hơ n n ữ a, không có gì sai đ ố i v ớ i m ộ t quan h ệ SQL có trùng l ặ p. Nế u chúng ta không mu ố n có s ự trùng l ặ p trong k ế t qu ả , ta có th ể ti ế p theo sau từ khoá SELECT b ằ ng t ừ khoá DISTINCT. T ừ đó nói v ớ i SQL chỉ t ạ o ra m ộ t b ả n sao cho m ộ t b ộ giá tr ị . Ch ẳ ng h ạ n SELECT DISTINCT Lươ ng FROM NHÂNVIÊN ; 1.4.2 Trùng lặ p trong phép h ợ p, phép giao và phép tr ừ Không giố ng nh ư l ệ nh SELECT gi ữ gìn s ự trùng l ặ p nh ư m ặ c đ ị nh và ch ỉ loạ i b ỏ chúng khi đ ư a vào t ừ khoá DISTINCT, các phép toán h ợ p, giao và trừườ th ng lo ạỏự i b s trùng l ặ p. Nh ưậ v y, các túi đ ượổ c đ i thành t ậợ p h p và bảậợủ n t p h p c a phép toán đ ượ c áp d ụể ng. Đ ngăn ng ừệạỏ a vi c lo i b trùng lặ p, chúng ta ph ả i vi ế t sau các phép toán UNION, INTERSECT, EXCEPT từ khoá ALL. N ế u chúng ta làm nh ư v ậ y thì chúng ta nh ậ n đ ượ c cú pháp túi thông qua các phép toán này. Ví dụ 26: Xét biể u th ứ c h ợ p c ủ a ví d ụ 14 nh ư ng có thêm vào t ừ khoá ALL: (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 1) UNION ALL (SELECT Mãsố NV FROM NHÂNVIÊN_DỰ ÁN WHERE Mãsố DA = 2) Kế t qu ả Mãsố NV NV001 NV001 NV002 NV018 NV018 35
Ta thấ y bây gi ờ trong k ế t qu ả xu ấ t hi ệ n các b ộ trùng nhau. N ế u m ộ t nhân viên làm việ c cho c ả d ự án 1 và d ự án 2 thì mã s ố c ủ a nhân viên đó xuấ t hi ệ n trong k ế t qu ả hai l ầ n. Cũng như đ ố i v ớ i UNION, các phép toán INTERSECT ALL và EXCEPT ALL là giao và trừ c ủ a các túi (bag). Nh ư v ậ y, n ế u R và S là các quan h ệ thì kế t qu ả c ủ a bi ể u th ứ c R INTERSECT ALL S là mộ t quan h ệ trong đó s ốầấệủộộ l n xu t hi n c a m t b t là s ốỏấủ nh nh t c a sốầấệủộ l n xu t hi n c a b đó trong R và s ốầấệủộ l n xu t hi n c a b đó trong S. Kế t qu ả c ủ a bi ể u th ứ c R EXCEPT ALL S là mộ t quan h ệ trong đó s ốầấệộằốầấệủ l n xu t hi n b t b ng s l n xu t hi n c a nó trong R trừốầấệủ đi s l n xu t hi n c a nó trong S v ớềệệố i đi u ki n hi u s này là dươ ng. 1.4.3 Nhóm và sự k ế t h ợ p trong SQL Phép toán nhóm và kế t h ợ p trong đ ạ i s ố quan h ệ cho phép ta phân chia các bộ c ủ a m ộ t quan h ệ thành các nhóm d ự a trên các giá tr ị c ủ a m ộ t ho ặ c nhiề u thu ộ c tính trong các b ộ . Sau đó chúng ta có th ể k ế t h ợ p m ộ t s ố các cộ t khác c ủ a quan h ệ b ằ ng cách áp d ụ ng phép toán k ế t h ợ p đ ố i v ớ i các cộ t đó. N ế u có các nhóm thì phép k ế t h ợ p s ẽ đ ượ c th ự c hi ệ n riêng r ẽ cho từ ng nhóm. SQL cung c ấ p m ọ i kh ả năng c ủ a phép toán trên thông qua vi ệ c sử d ụ ng các phép toán nhóm trong m ệ nh đ ề SELECT và m ộ t m ệ nh đ ề GROUP BY đặ c bi ệ t. 1.4.4 Các phép toán nhóm SQL sử d ụ ng 5 phép toán nhóm SUM, AVG, MIN, MAX, và COUNT. Các phép toán này đượ c s ử d ụ ng b ằ ng cách áp d ụ ng chúng cho các bi ể u thứ c có giá tr ịướườ vô h ng, th ng là m ộ t tên c ộở t, trong m ệề nh đ SELECT. Có mộ t ngo ạ i l ệ là bi ể u th ứ c COUNT(*), bi ể u th ứ c này đ ế m tấả t c các b ộ trong m ộ t quan h ệượếậừệề đ c thi t l p t m nh đ FROM và mệ nh đ ề WHERE c ủ a truy v ấ n. 36
Hơ n n ữ a, chúng ta có tuỳ ch ọ n lo ạ i tr ừ trùng l ặ p ra kh ỏ i c ộ t tr ướ c khi áp dụ ng phép toán nhóm b ằ ng vi ệ c s ử d ụ ng t ừ khoá DISTINCT. Nh ư v ậ y, mộ t bi ể u th ứ c nh ư là COUNT(DISTINCT x) đ ế m s ố các giá tr ị khác nhau trong cộ t x. Chúng ta có th ể s ử d ụ ng các phép toán khác ở v ị trí c ủ a COUNT ở đây nh ư ng bi ể u th ứ c nh ư SUM(DISTINCT x) th ườ ng không có ý nghĩa mấ y, b ở i vì nó yêu c ầ u ta tính t ổ ng các giá tr ị khác nhau trong c ộ t x. Ví dụ 27: Truy vấ n sau đây tìm giá tr ị l ươ ng trung bình c ủ a t ấ t c ả các nhân viên: SELECT AVG(Lươ ng) FROM NHÂNVIÊN ; Chú ý rằ ng ở đây không có m ệ nh đ ề WHERE. Truy v ấ n này xem xét c ộ t Lươ ng c ủ a quan h ệ NHÂNVIÊN, tính t ổ ng các giá tr ị tìm đ ượ c ở đây, m ộ t giá trị cho m ỗ i b ộ (cho dù n ế u b ộ là trùng l ặ p c ủ a m ộ t vài b ộ khác), và chia tổ ng s ố cho s ố các b ộ . N ế u không có các b ộ trùng l ặ p thì truy v ấ n này cho lươ ng trung bình nh ư chúng ta mong đ ợ i. N ế u có các b ộ trùng l ặ p, thì mộ t giá tr ị l ươ ng trùng l ặ p n l ầ n s ẽ đ ượ c tính n l ầ n trong trung bình. Ví dụ 28: Truy vấ n sau đây: SELECT COUNT(*) FROM NHÂNVIÊN ; đế m s ố các b ộ trong quan h ệ NHÂNVIÊN. Truy vấ n t ươ ng t ự : SELECT COUNT(Lươ ng) FROM NHÂNVIÊN ; đếốị m s giá tr trong c ộươủ t L ng c a quan h ệở . B i vì các giá tr ị trùng l ặ p không bị lo ạ i b ỏ khi chúng ta chi ế u lên c ộ t L ươ ng trong SQL, t ổ ng đ ế m này sẽ gi ố ng nh ư t ổ ng đ ế m do truy v ấ n v ớ i COUNT(*) sinh ra. 37
Nế u chúng ta mu ố n ch ắ c ch ắ n r ằ ng ta không đ ế m các giá tr ị trùng l ặ p quá mộ t l ầ n, chúng ta có th ể s ử d ụ ng t ừ khoá DISTINCT tr ướ c thu ộ c tính nhóm, như : SELECT COUNT(DISTINCT Lươ ng) FROM NHÂNVIÊN ; Bây giờỗươẽượếộầ m i l ng s đ c đ m m t l n, không c ầ n quan tâm đ ếệ n vi c nó xuấ t hi ệ n trong bao nhiêu b ộ . 1.4.5 Nhóm Để nhóm các b ộ , chúng ta s ử d ụ ng m ệ nh đ ề GROUP BY, đi sau m ệ nh đ ề WHERE. Theo sau từ khoá GROUP BY là m ộ t danh sách các thu ộ c tính nhóm. Trong hoàn cả nh đ ơ n gi ả n nh ấ t, ch ỉ có m ộ t tham chi ế u quan h ệ trong mệ nh đ ề FROM, và quan h ệ này có các b ộ c ủ a nó đ ượ c nhóm theo theo các giá trị c ủ a chúng trong các thu ộ c tính nhóm. Dù phép toán nhóm nào đượ c s ử d ụ ng trong m ệ nh đ ề SELECT cũng ch ỉ đ ượ c áp d ụ ng bên trong các nhóm. Ví dụ 29: Vấ n đ ề tìm trong quan h ệ NHÂNVIÊN t ổ ng l ươ ng theo t ừ ng đơ n v ị : SELECT Mãsố ĐV, SUM(L ươ ng) FROM NHÂNVIÊN GROUP BY Mãsố ĐV ; Chúng ta có thể t ưở ng t ượ ng là các b ộ c ủ a quan h ệ NHÂNVIÊN đ ượ c sắếạượ p x p l i và đ c nhóm sao cho t ấ t các các b ộốớơị đ i v i đ n v 1 là cùng vớ i nhau, t ấ t c ả các b ộ c ủ a đ ơ n v ị 4 là cùng v ớ i nhau, . Các t ổ ng c ủ a các thành phầ n L ươ ng c ủ a các b ộ trong t ừ ng nhóm đ ượ c tính toán, Mãsố ĐV đ ượ c đ ư a ra cùng v ớ i t ổ ng đó. Quan sát ví dụ 29 ta th ấ y m ệ nh đ ề SELECT có hai lo ạ i s ố h ạ ng: 1. Các kế t h ợ p, ở đó m ộ t phép toán nhóm đ ượ c áp d ụ ng cho m ộ t thu ộ c tính hoặ c m ộ t bi ể u th ứ c bao g ồ m các thu ộ c tính. Nh ư đã đ ề c ậ p đ ế n, các số h ạ ng này đ ượ c tính giá tr ị trên c ơ s ở t ừ ng nhóm. Trong ví d ụ này, SUM(Lươ ng) là m ộ t k ế t h ợ p. 38
2.Các thuộ c tính, ch ẳ ng h ạ n nh ư Mãs ố ĐV trong ví d ụ này, xu ấ t hi ệ n trong mệ nh đ ề GROUP BY. Trong m ộ t m ệ nh đ ề SELECT có các phép toán nhóm, chỉ nh ữ ng thu ộ c tính nào đ ượ c đ ề c ậ p đ ế n trong m ệ nh đ ề GROUP BY mớ i có th ể xu ấ t hi ệ n nh ư các thu ộ c tính không nhóm trong mệ nh đ ề SELECT. Khi các truy vấ n có ch ứ a GROUP BY nói chung có c ả các thu ộ c tính nhóm và sự k ế t h ợ p trong m ệ nh đ ề SELECT, v ề m ặ t k ỹ thu ậ t không c ầ n thiế t có m ặ t c ả hai. Ví d ụ , chúng ta có th ể vi ế t: SELECT Mãsố ĐV FROM NHÂNVIÊN GROUP BY Mãsố ĐV; Truy vấ n này s ẽ nhóm các b ộ c ủ a NHÂNVIÊN theo mã s ố đ ơ n v ị c ủ a nó và sau đó in ra mã số đ ơ n v ị cho m ỗ i nhóm, không c ầ n quan tâm đ ế n có bao nhiêu bộ có cùng mã s ố đ ơ n v ị . Nh ư v ậ y, truy v ấ n ở trên có cùng k ế t quả nh ư SELECT DISTINCT Mãsố ĐV FROM NHÂNVIÊN ; Có thể s ử d ụ ng m ệ nh đ ề GROUP BY trong m ộ t truy v ấ n v ớ i nhi ề u quan hệ . Các truy v ấưậượểệằ n nh v y đ c th hi n b ng dãy các b ướ c sau đây: 1. Tính quan hệ R đ ượ c bi ể u di ễ n b ằ ng các m ệ nh đ ề FROM và WHERE. Như v ậ y, quan h ệ R là tích Đ ề cac c ủ a các quan h ệ đ ượ c ch ỉ ra trong mệ nh đ ề FROM và áp d ụ ng phép ch ọ n c ủ a m ệ nh đ ề WHERE đ ố i v ớ i nó. 2.Nhóm các bộ c ủ a R theo các thu ộ c tính trong m ệ nh đ ề GROUP BY. 3. Kế t qu ả là các thu ộ c tính và các k ế t h ợ p c ủ a m ệ nh đ ề SELECT đ ượ c tạ o ra c ứ nh ư là truy v ấ n trên m ộ t quan h ệ đ ượ c l ư u tr ữ R. Ví dụ 30: Giả s ử chúng ta mu ố n đ ư a ra tên đ ơ n v ị và s ố l ượ ng các nhân viên trong từ ng đ ơ n v ị . Chúng ta c ầ n l ấ y thông tin t ừ hai quan h ệ : NHÂNVIÊN và ĐƠ NV Ị . Chúng ta b ắ t đ ầ u b ằ ng cách n ố i têta chúng b ằ ng cách so sánh bằ ng các mã s ố đ ơ n v ị t ừ hai quan h ệ . B ướ c này cho chúng ta mộ t quan h ệ mà trong đó m ỗộƠỊượặớ i b Đ NV đ c c p v i các b ộ 39
NHÂNVIÊN có mã số đ ơ n v ị gi ố ng v ớ i mã s ố đ ơ n v ị c ủ a nó. Bây gi ờ , chúng ta có thể nhóm các b ộ đ ượ c ch ọ n c ủ a quan h ệ này theo tên c ủ a đ ơ n vị . Cu ố i cùng, chúng ta đ ế m s ố các nhân viên trong t ừ ng nhóm. Truy v ấ n đượ c vi ế t nh ư sau: SELECT TênĐV, COUNT(*) FROM NHÂNVIÊN NV, ĐƠỊ NV ĐV WHERE NV.Mãsố ĐV = ĐV.Mãs ố ĐV GROUP BY TênĐV ; Kế t qu ả TênĐV COUNT(*) Nghiêncứ u 4 Hànhchính 3 Lãnhđạ o 1 1.4.6 Các mệ nh đ ề HAVING Giả s ử r ằ ng chúng ta không mu ố n tính đ ế n t ấ t c ả các tên đ ơ n v ị trong bả ng c ủ a chúng ta ở ví d ụ 30 ở trên. Chúng ta có th ể h ạ n ch ế các b ộ trướ c khi nhóm theo cách có th ể làm r ỗ ng các nhóm không mong mu ố n. Ví dụế , n u chúng ta ch ỉốố mu n s các nhân viên c ủộơịảớơ a m t đ n v ph i l n h n hoặ c b ằ ng 3. Khi đó, chúng ta ti ế p theo m ệ nh đ ề GROUP BY m ộ t m ệ nh đề HAVING. M ệ nh đ ề HAVING bao g ồ m t ừ khoá HAVING theo sau là mộ t đi ề u ki ệ n v ề nhóm. Ví dụ 31: Giả s ử chúng ta mu ố n in ra Tên đ ơ n v ị và s ố nhân viên trong từơịốớữơị ng đ n v đ i v i nh ng đ n v nào có nhi ềơặằ u h n ho c b ng 3 nhân viên. Chúng ta có thể thêm vào ví d ụ 30 m ệ nh đ ề HAVING COUNT(*) >= 3 ; Truy vấ n k ế t qu ả đ ượ c cho nh ư d ướ i đây: Kế t qu ả TênĐV COUNT(*) Nghiêncứ u 4 40
Hànhchính 3 Chúng ta phả i nh ớ m ộ t s ố quy t ắ c v ề các m ệ nh đ ề HAVING: . Mộ t phép nhóm trong m ệ nh đ ề HAVING ch ỉ áp d ụ ng đ ố i v ớ i các b ộ c ủ a nhóm đã đượ c ki ể m tra. . Bấ t kỳ thu ộ c tính nào c ủ a các quan h ệ trong m ệ nh đ ề FROM đ ề u có th ể đượ c nhóm trong m ệ nh đ ề HAVING, nh ư ng ch ỉ có các thu ộ c tính có th ể xuấ t hi ệ n trong danh sách GROUP BY không đ ượ c nhóm trong m ệ nh đ ề HAVING (cùng quy tắ c nh ư v ớ i m ệ nh đ ề SELECT). Mộ t s ố đi ề u c ầ n nh ớ : * Thứ t ự c ủ a các m ệ nh đ ề trong các truy v ấ n SQL: Cho đế n bây gi ờ chúng ta đã g ặ p t ấ t c ả sáu m ệ nh đ ề trong m ộ t truy v ấ n “select-from-where”: SELECT, FROM, WHERE, GROUP BY, HAVING và ORDER BY. Chỉ có hai m ệ nh đ ề đ ầ u là b ắ t bu ộ c, nh ư ng ta không th ể sử d ụ ng m ệ nh đ ề HAVING mà không có m ệ nh đ ề GROUP BY. B ấ t kỳ mệ nh đ ề ph ụ thêm nào cũng ph ả i xu ấ t hi ệ n theo th ứ t ự ở trên. * Nhóm, Tậ p h ợ p và Null: Khi các bộ có giá tr ị Null, c ầ n nh ớ m ộ t s ố quy t ắ c sau: • Giá trị Null đ ượ c l ờ đi trong t ậ p h ợ p. Nó không góp ph ầ n vào sum, average, hoặ c count hoặ c không là min hoặ c max trong cộ t c ủ a chúng. Ví dụ , COUNT(*) luôn luôn là m ộ t phép đ ế m c ủ a s ố các b ộ trong m ộ t quan hệ , nh ư ng COUNT(A) là s ố các b ộ v ớ i giá tr ị c ủ a thu ộ c tính A không Null. • Mặ t khác, NULL đ ượ c x ử lý nh ư là m ộ t giá tr ị thông th ườ ng trong m ộ t thuộ c tính nhóm. Ví d ụ , SELECT a, AVG(b) FROM R s ẽ t ạ o ra m ộ t b ộ vớ i NULL cho giá tr ị c ủ a a và giá tr ị trung bình c ủ a b đ ố i v ớ i các b ộ vớ i a =NULL, n ế u có ít nh ấ t m ộ t b ộ trong R v ớ i thành ph ầ n a là NULL. 41
1.5 SỬỔƠỞỮỆ A Đ I C S D LI U Ngoài dạ ng truy v ấ n SQL chu ẩ n select-from-where, có m ộ t s ố các d ạ ng lệ nh khác không tr ả l ạ i m ộ t k ế t qu ả nh ư ng làm thay đ ổ i tr ạ ng thái c ủ a quan hệ . Trong ph ầ n này chúng ta s ẽ h ướ ng đ ế n các d ạ ng l ệ nh cho phép ta - Chèn các bộ vào m ộ t quan h ệ - Xoá mộ t s ố b ộ ra kh ỏ i quan h ệ - Cậậ p nh t các giá tr ịủộố c a m t s thành ph ầủộốộồạ n c a m t s b đã t n t i. Các phép toán như v ậ y g ọ i là các phép toán s ử a đ ổ i. 1.5.1 Chèn Dạ ng c ơ b ả n c ủ a l ệ nh chèn bao g ồ m: 1. Từ khoá INSERT TO 2.Tên củ a m ộ t quan h ệ R 3.Mộ t danh sách các thu ộ c tính c ủ a quan h ệ R đ ặ t trong d ấ u ngo ặ c 4.Từ khoá VALUES 5.Mộểứộ t bi u th c b , nghĩa là m ộ t danh sách các giá tr ịụểượặ c th đ c đ t trong dấ u ngo ặ c, m ộ t giá tr ị cho m ỗ i thu ộ c tính ở trong danh sách ở đi ể m 3. Như v ậ y, d ạ ng chèn c ơ b ả n là INSERT INTO R(A1,A2, ,An) VALUES (v1,v2, ,vn) Mộ t b ộ đ ượ c t ạ o ra b ằ ng cách s ử d ụ ng giá tr ị vi cho thuộ c tính Ai, vớ i i = 1,2, ,n Nế u danh sách c ủ a các thu ộ c tính không bao hàm t ấ t c ả các thu ộ c tính củ a quan h ệ R thì b ộượạ đ c t o ra có các giá tr ịầị ng m đ nh cho t ấả t c các thuộ c tính b ị thi ế u. Giá tr ị ng ầ m đ ị nh hay dùng là NULL, nh ư ng cũng có thể có các tuỳ ch ọ n khác. Ví dụ 32: 1) INSERT INTO ĐƠ NV Ị (Mãs ố ĐV, TênĐV, Mãs ố NQL, Ngàybắ tđ ầ u) 42
2) VALUES (8,’Kế ho ạ ch’, ‘NV018’, ‘1977-16-24’); Kếảủệựệệ t qu c a vi c th c hi n l nh này là m ộộớố t b v i b n thành ph ầở n dòng 2) sẽ đ ượ c chèn vào quan h ệ Đ Ơ NV Ị . Vì t ấ t c ả các thu ộ c tính c ủ a ĐƠ NV Ị đã đ ượ c k ể đ ế n ở dòng 1) nên không c ầ n ph ả i thêm vào các thành phầ n ng ầ m đ ị nh. Các giá tr ị trên dòng 2) phù h ợ p v ớ i các thu ộ c tính trên dòng 1) theo thứự t cho tr ướưậếạ c, nh v y, ‘K ho ch’ tr ở thành giá tr ịủ c a thuộ c tính TênĐV. Nế u chúng ta cung c ấ p t ấ t c ả các giá tr ị cho t ấ t c ả các thu ộ c tính c ủ a quan hệ thì chúng ta có th ể b ỏ qua danh sách các thu ộ c tính đi theo sau tên quan hệ . Ví d ụ , chúng ta có th ể vi ế t l ạ i l ệ nh trên d ướ i d ạ ng INSERT INTO ĐƠỊ NV VALUES (8,’Kế ho ạ ch’, ‘NV018’, ‘1977-16-24’); Tuy nhiên, nế u chúng ta l ấ y tuỳ ch ọ n này thì ta ph ả i ch ắ c ch ắ n r ằ ng th ứ tựủ c a các giá tr ịốưứựẩủ là gi ng nh th t chu n c a các thu ộ c tính đ ốớ i v i quan hệế . N u ta không ch ắắềứựẩốớ c ch n v th t chu n đ i v i các thu ộ c tính thì tố t nh ấ t là li ệ t kê chúng trong m ệ nh đ ề INSERT đ ể ta ch ọ n cho các giá trị c ủ a nó trong m ệ nh đ ề VALUES. Lệ nh INSERT đ ơảượ n gi n đ c mô t ảở trên ch ỉặộộ đ t m t b vào m ộ t quan hệ . Thay vì s ử d ụ ng các giá tr ị rõ cho m ộ t b ộ , chúng ta có th ể tính toán mộậợ t t p h p các b ộượ đ c chèn vào b ằ ng cách s ửụộ d ng m t truy v ấ n con. Truy vấ n con này thay th ế t ừ khoá VALUES và bi ể u th ứ c b ộ trong d ạ ng lệ nh INSERT đ ượ c mô t ả ở trên. Ví dụ 33: Giả s ử chúng ta có m ộ t quan h ệ Đ Ơ NV Ị 1 ch ứ a các b ộ giá tr ị cùng kiể u v ớ i các b ộ trong quan h ệ Đ Ơ NV Ị . N ế u mu ố n chèn t ấ t c ả các bộ c ủ a Đ Ơ NV Ị 1 vào quan h ệ Đ Ơ NV Ị ta vi ế t l ệ nh chèn nh ư sau: 1) INSERT INTO ĐƠỊ NV 2) SELECT * 3) FROM ĐƠỊ NV 1 ; 43
1.5.2 Xóa Mộ t l ệ nh xoá bao g ồ m 1. Các từ khoá DELETE FROM 2.Tên củ a m ộ t quan h ệ , R 3.Từ khoá WHERE và 4. mộ t đi ề u ki ệ n. Như v ậ y, d ạ ng c ủ a phép xoá là DELETE FROM R WHERE ; Hậảủệựệệ u qu c a vi c th c hi n l nh này là m ỗộả i b tho mãn đi ềệẽị u ki n s b xoá khỏ i quan h ệ . Ví dụ 34: Chúng ta có thể xoá kh ỏ i quan h ệ Đ Ơ NV Ị b ộ giá tr ị (8,’Kế ho ạ ch’, ‘NV018’, ‘1977-16-24’) b ằ ng l ệ nh SQL sau đây: DELETE FROM ĐƠỊ NV WHERE TênĐV = ‘Kế ho ạ ch’; Chú ý rằ ng không gi ố ng nh ư l ệ nh chèn ở ví d ụ 32, chúng ta không th ể ch ỉ ra mộ t cách đ ơảộộẽị n gi n m t b s b xoá. Đúng h ơ n, chúng ta ph ảảộ i mô t b mộ t cách chính xác b ằ ng m ệ nh đ ề WHERE. Ví dụ 35 Lệ nh sau đây DELETE FROM NHÂNVIÊN WHERE Lươ ng < 3000 ; sẽ xoá kh ỏ i quan h ệ NHÂNVIÊN t ấ t c ả các b ộ giá tr ị có thành ph ầ n Lươ ng nh ỏ h ơ n 3000. 1.5.3 Cậ p nh ậ t Lệ nh update trong SQL làm thay đ ổ i m ộ t s ố thành ph ầ n c ủ a các b ộ đã tồ n t ạ i trong c ơ s ở d ữ li ệ u. D ạ ng t ổ ng quát c ủ a l ệ nh update là: 1.Từ khoá UPDATE 2. Mộ t tên quan h ệ , R 44
3.Từ khoá SET 4. Mộ t danh sách các công th ứ c, m ỗ i công th ứ c đ ặ t m ộ t thu ộ c tính c ủ a quan hệằ R b ng m ộ t giá tr ịủộểứặộằ c a m t bi u th c ho c m t h ng. 5. Từ khoá WHERE và 6. Mộ t đi ề u ki ệ n. Như v ậ y d ạ ng l ệ nh update là UPDATE R SET WHERE ; Mỗ i m ộ t là m ộ t thu ộ c tính, m ộ t d ấ u b ằ ng và m ộ t công thứ c. N ế u có nhi ề u h ơ n m ộ t phép gán thì chúng đ ượ c phân cách nhau bằ ng d ấ u ch ấ m ph ả y (;). Hậ u qu ả c ủ a l ệ nh này là tìm t ấ t c ả các b ộ giá tr ị trong R tho ả mãn đi ề u kiệ n. M ỗ i b ộ này sau đó s ẽ đ ượ c thay đ ổ i b ằ ng cách tính giá tr ị các công thứ c và gán cho các thành ph ầ n c ủ a b ộ v ớ i các thu ộ c tính t ươ ng ứ ng c ủ a R. Vídụ 36: Hãy sử a đ ổ i TênSV c ủ a các b ộ trong quan h ệ Đ Ơ NV Ị có tên đơ n v ị là ‘Hànhchính’ thành tên m ớ i là ‘K ế ho ạ ch’. Ta vi ế t l ệ nh UPDATE như sau: 1) UPDATE ĐƠỊ NV 2) SET TênĐV = ‘Kế ho ạ ch’ 3) WHERE TênĐV = ‘Hànhchính’ ; Dòng 3) kiể m tra r ằ ng có ph ả i tên đ ơ n v ị là ‘Hànhchính’ hay không. N ế u đúng, dòng 2) sẽ thay th ế tên này b ằ ng ‘K ế ho ạ ch’. 1.6 ĐỊ NH NGHĨA M Ộ T L ƯỢ C Đ Ồ QUAN H Ệ TRONG SQL Trong phầ n này chúng ta s ẽ th ả o lu ậ n v ề đ ị nh nghĩa d ữ li ệ u, m ộ t ph ầ n củ a ngôn ng ữ SQL cho phép mô t ả các c ấ u trúc thông tin trong c ơ s ở d ữ liệ u. Ng ượ c l ạ i, các khía c ạ nh SQL th ả o lu ậ n tr ướ c kia - các truy v ấ n và cậ p nh ậ t- th ườ ng đ ượ c g ọ i là thao tác d ữ li ệ u. 45
Chủềủầ đ c a ph n này là mô t ả các l ượồủ c đ c a các quan h ệượư đ c l u giữ . Chúng ta s ẽ th ấ y mô t ả m ộ t quan h ệ (b ả ng) m ớ i, m ộ t khung nhìn như th ế nào. 1.6.1 Các kiể u d ữ li ệ u Trướ c tiên chúng ta đ ư a ra các ki ểữệ u d li u nguyên t ửượệố đ c h th ng SQL hỗ tr ợ . M ọ i thu ộ c tính ph ả i có m ộ t ki ể u d ữ li ệ u. 1. Các xâu ký tự có đ ộ dài thay đ ổ i ho ặ c c ố đ ị nh. Ki ể u CHAR(n) ký hi ệ u mộ t xâu g ồ m n ký t ự . Nh ư v ậ y, n ế u m ộ t thu ộ c tính có ki ể u CHAR(n) thì trong mộ t b ộ b ấ t kỳ, thành ph ầ n cho thu ộ c tính này là m ộ t xâu g ồ m n ký tự . VARCHAR(n) ký hi ệ u m ộ t xâu g ồ m nhi ề u nh ấ t là n ký t ự . Các thành phầ n cho các thu ộ c tính thu ộ c ki ể u này s ẽ là m ộ t xâu có t ừ 0 đ ế n n ký t ự . SQL cho phép các ép buộ c h ợ p lý gi ữ a các giá tr ị c ủ a các ki ể u xâu ký t ự . Thườườ ng th ng, các xâu có đ ộốịượ dài c đ nh đ c thêm vào các d ấả u kho ng trố ng n ế u giá tr ị c ủ a nó nh ỏ h ơ n đ ộ dài cho phép. Khi so sánh m ộ t xâu v ớ i mộ t xâu khác, các d ấ u tr ố ng thêm vào s ẽ đ ượ c b ỏ qua. 2. Các xâu bit có độ dài c ố đ ị nh ho ặ c thay đ ổ i. Các xâu này t ươ ng t ự nh ư các xâu ký tự có đ ộ dài c ố đ ị nh ho ặ c thay đ ổ i, nh ư ng giá tr ị c ủ a chúng là các xâu bit. BIT(n) ký hiệ u các xâu bit có đ ộ dài n, trong khi đó BIT VARYING(n) ký hiệ u các xâu bit có đ ộ dài nh ỏ h ơ n ho ặ c b ằ ng n. 3. Kiể u BOOLEAN ký hi ệ u các thu ộ c tính có giá tr ị lô gic. Các giá tr ị có thể có c ủ a thu ộ c tính thu ộ c lo ạ i này là TRUE, FALSE và UNKNOWN. 4. Kiể u INT ho ặ c INTEGER ký hi ệ u các giá tr ị nguyên. Ki ể u SHORTINT cũng ký hiệ u các giá tr ị nguyên nh ư ng có s ố các ch ữ s ố ít h ơ n. 5. Các số d ấ u ph ả y đ ộ ng có th ể bi ể u di ễ n b ằ ng nhi ề u cách. Chúng ta s ử dụ ng ki ể u REAL ho ặ c FLOAT (hai ki ể u này cùng nghĩa) đ ố i v ớ i các s ố dấảộộ u ph y đ ng. Đ chính xác cao h ơ n có th ểậượớể nh n đ c v i ki u DOUBLE PRECISION. SQL cũng có các kiể u v ớ i các s ố th ự c d ấ u ph ả y cố đ ị nh. Đó là ki ể u DECIMAL(n,d) cho phép các giá tr ị ch ứ a n ch ữ s ố thậ p phân, v ớịủấấậ i v trí c a d u ch m th p phân đ ượảếị c gi thi t là v trí d k ể từ bên ph ả i sang. Ki ể u NUMERIC h ầ u nh ư đ ồ ng nghĩa v ớ i DECIMAL. 46
6. Ngày và giờ cũng có th ể đ ượ c bi ể u di ễ n nh ờ các ki ể u DATE và TIME. Các giá trị c ủ a chúng là các xâu ký t ự d ạ ng đ ặ c bi ệ t. Th ự c v ậ y, chúng ta có thể bi ế n đ ổ i ngày và gi ờ thành ki ể u xâu và ng ượ c l ạ i. 1.6.2 Các khai báo bả ng đ ơ n gi ả n Dạơảấủ ng đ n gi n nh t c a khai báo m ộượồ t l c đ quan h ệ bao g ồ m các t ừ khoá CREATE TABLE sau đó là tên củ a quan h ệ và m ộ t danh sách các thuộ c tính cùng v ớ i ki ể u c ủ a chúng đ ượ c đ ặ t trong d ấ u ngo ặ c. Ví dụ 1.37 L ượ c đ ồ quan h ệ Đ Ơ NV Ị bi ể u th ị trong SQL nh ư sau: 1) CREATE TABLE ĐƠỊ NV ( 2) Mãsố ĐV INT, 3) TênĐV VARCHAR(15), 4) Mãsố NQL CHAR(9), 5) Ngàybắ tđ ầ u DATE ); Thuộ c tính đ ầ u tiên, Mãs ố ĐV là m ộ t s ố nguyên. Thu ộ c tính th ứ hai là xâu ký tự có đ ộ dài nh ỏ h ơ n ho ặ c b ằ ng 15. Thu ộ c tính th ứ ba là m ộ t xâu có độ dài c ốịồ đ nh g m 9 ký. Nh ưậế v y n u có m ộ t mã s ốườả ng i qu n lý không có đủ 9 ký t ự thì nó s ẽượệốư đ c h th ng đ a thêm vào m ộốả t s kho ng trố ng, còn m ộ t mã s ố có quá 9 ký t ự thì s ẽ b ị ch ặ t b ớ t đi. Cu ố i cùng, thu ộ c tính Ngàybắ tđ ầ u ki ể u DATE. Trong SQL chu ẩ n không có ki ể u này, chúng ta thay nó bằ ng CHAR(10). 1.6.3 Sử a đ ổ i các l ượ c đ ồ quan h ệ - Chúng ta có thể lo ạ i b ỏ m ộ t quan h ệ R b ằ ng l ệ nh SQL: DROP TABLE R; Quan hệẽ R s không còn là m ộầủượồơởữệ t ph n c a l c đ c s d li u và chúng ta không còn có thể truy c ậ p đ ế n các b ộ giá tr ị c ủ a nó n ữ a. - Thông thườ ng chúng ta hay s ửổượồủộ a đ i l c đ c a m t quan h ệồạ đã t n t i hơ n là xoá b ỏộ m t quan h ệ là m ộầủơởữệồạ t ph n c a c s d li u t n t i lâu dài. Nhữ ng s ựửổ s a đ i này đ ượựệằ c th c hi n b ng m ộệ t l nh b ắầớừ t đ u v i t khoá 47
ALTER TABLE và tên củ a quan h ệ . Sau đó chúng ta có nhi ề u tuỳ ch ọ n, quan trọ ng nh ấ t là 1. ADD sau đó là mộ t tên c ộ t và ki ể u c ủ a nó. 2. DROP sau đó là mộ t tên c ộ t. Ví dụ 38: Chúng ta có thể thêm vào quan h ệ Đ Ơ NV Ị S ố đi ệ ntho ạ i và b ỏ đi thuộ c tính Ngàyb ắ tđ ầ u b ằ ng các l ệ nh sau: DROP TABLE ĐƠ NV Ị ADD S ố đi ẹ ntho ạ i CHAR(10); DROP TABLE ĐƠ NV Ị DROP Ngàyb ắ tđ ầ u ; Kếả t qu là quan h ệƠỊượ Đ NV đ c thêm vào m ộộ t thu c tính S ốệạ đi ntho i, đó là mộ t xâu ký t ự có đ ộ dài c ố đ ị nh g ồ m 10 ký t ự . Trong quan h ệ hi ệ n tạ i, các b ộịề giá tr đ u có các thành ph ầốớốệạư n đ i v i S đi ntho i nh ng chúng ta biếằ t r ng không có s ốệạ đi n tho i nào đ ượặ c đ t vào đó. Nh ưậ v y, giá tr ị củ a m ỗ i thành ph ầ n s ẽ là NULL. Sau đây chúng ta s ẽ th ấ y có kh ả năng chọ n m ộ t giá tr ị “ng ầ m đ ị nh” thay cho NULL đ ố i v ớ i các giá tr ị không biế t. 1.6.4 Các giá trị ng ầ m đ ị nh Khi chúng ta tạ o ra ho ặ c s ử a đ ổ i các b ộ giá tr ị , đôi lúc chúng ta không có các giá trị cho t ấ t c ả các thành ph ầ n. Nh ư ví d ụ ở trên, khi ta thêm m ộ t c ộ t vào mộ t quan h ệ , các b ộ giá tr ị đang t ồ n t ạ i s ẽ không có giá tr ị cho thu ộ c tính đó và NULL đượ c s ử d ụ ng đ ể thay th ế cho giá tr ị “th ự c”. Tuy nhiên SQL còn cung cấ p kh ả năng ch ọ n giá tr ị ng ầ m đ ị nh, m ộ t giá tr ị xu ấ t hi ệ n trong cộ t m ỗ i khi không các giá tr ị khác đ ượ c bi ế t. Nói chung, bấ t kỳ ch ỗ nào chúng ta khai báo m ộ t thu ộ c tính và ki ể u d ữ liệ u c ủ a nó chúng ta có th ể thêm vào t ừ khoá DEFAUL và m ộ t giá tr ị thích hợ p. Giá tr ị đó ho ặ c là NULL ho ặ c là m ộ t h ằ ng. M ộ t s ố các giá tr ị khác cũng đượệố c h th ng cung c ấườ p nh là th i gian hi ệạặộ n t i, ho c m t tuỳ chọ n. Vídụ 39 Xét ví dụ 37 ở trên. Chúng ta có th ể s ử d ụ ng ký t ự ? làm ng ầ m đị nh cho Mãs ố NQL, s ử d ụ ng ‘0000-00-00’ làm ng ầ m đ ị nh cho Ngàybắ tđ ầ u, ta vi ế t nh ư sau: 48
4) Mãsố NQL CHAR(9) DEFAULT ‘?’, 5) Ngàybắ tđ ầ u DATE DEFAULT DATE’0000-00-00’ 1.6.5 Các chỉ s ố Mộỉố t ch s trên m ộộ t thu c tính A c ủộ a m t quan h ệộấ là m t c u trúc d ữ liệ u làm có hi ệảệ u qu vi c tìm các b ộị giá tr có giá tr ịốịốớộ c đ nh đ i v i thu c tính A. Các chỉ s ố th ườ ng giúp đ ỡ v ớ i các truy v ấ n trong đó thu ộ c tính A củ a chúng ta đ ượ c so sánh v ớ i m ộ t h ằ ng, ví d ụ A = 3 ho ặ c A<=3. Khi các quan hệ là r ấ t l ớ n, vi ệ c quét t ấ t c ả các b ộ c ủ a quan h ệ đ ể tìm các bộả tho mãn m ộềệ t đi u ki n cho tr ướởấố c tr nên r t t n kém. Ví d ụ , xét truy vấ n sau đây: SELECT * FROM NHÂNVIÊN WHERE Tên = ‘Thanh’ AND Ngàysinh = ‘1965-08-23’ ; Giả s ử có 10000 b ộ NHÂNVIÊN, trong đó ch ỉ có 100 có tên là Thanh và có 10 bộ có tên là Thanh và ngày sinh là ‘1965-08-23’. Mộ t cách v ụềểựệ ng v đ th c hi n truy v ấ n này là nh ậấả n t t c 10000 b ộ và kiể m tra đi ề u ki ệ n c ủ a m ệ nh đ ề WHERE trên t ừ ng b ộ . M ộ t cách có hi ệ u quả h ơ n là ch ỉ nh ậ n 100 b ộ có tên là ‘Thanh’ và ki ể m tra t ừ ng b ộ xem nó có phả i là sinh vào ‘1965-08-23’ hay không. Nó s ẽ còn hi ệ u qu ả h ơ n n ế u chỉậộả nh n 10 b tho mãn c ảềệủệề hai đi u ki n c a m nh đ WHERE. Đi ề u đó sẽ làm đ ượ c nh ờ k ỹ thu ậ t ch ỉ s ố (index). Giả s ử chúng ta mu ố n có m ộ t ch ỉ s ố trên thu ộ c tính Tên đ ố i v ớ i quan h ệ NHÂNVIÊN, ta viế t CREATE INDEX TênIndex ON NHÂNVIÊN(Tên); Kế t qu ả là m ộ t ch ỉ s ố có tên là TênIndex s ẽ đ ượ c t ạ o ra trên thu ộ c tính Tên củ a quan h ệ NHÂNVIÊN. T ừ nay v ề sau, nh ữ ng truy v ấ n SQL có ch ỉ ra mộ t Tên có th ể đ ượ c b ộ x ử lý truy v ấ n SQL th ự c hi ệ n theo cách là ch ỉ nhữ ng b ộ nào c ủ a NHÂNVIÊN v ớ i Tên đ ượ c ch ỉ rõ là đ ượ c xem xét. Nh ư vậờ y, th i gian c ầểảờ n đ tr l i cho truy v ấẽảốấề n s gi m xu ng r t nhi u. 49
Thông thườ ng, m ộệảịơởữệ t h qu n tr c s d li u cho phép ta xây d ựộ ng m t chỉốơ s đ n trên nhi ềộ u thu c tính. Ki ểỉốấ u ch s này l y các giá tr ịủề c a nhi u thuộ c tính và tìm đ ượ c các b ộ v ớ i các giá tr ị đã cho đ ố i v ớ i các thu ộ c tính này. Ví dụ , chúng ta có th ể khai báo m ộ t ch ỉ s ố trên các thu ộ c tính c ủ a quan hệ NHÂNVIÊNN_D Ự ÁN nh ư sau: CREATE INDEX DA_NVIndex ON NHÂNVIÊN (Mãsố DA, Mãsố NV); Nế u chúng ta mu ố n xoá b ỏ ch ỉ s ố , ta s ử d ụ ng l ệ nh sau: DROP INDEX Ví dụ : DROP INDEX TênIndex ; 1.6.6 Nhậ p môn v ề vi ệ c l ự a ch ọ n các ch ỉ s ố Việự c l a ch ọ n các ch ỉố s đòi h ỏườ i ng i thi ếếơởữệ t k c s d li u ph ả i cân bằềếố ng nhi u y u t và trên th ựếệựọ c t vi c l a ch n này là m ộ t trong các s ự kiệ n chính có ảưởếệộếếơởữệ nh h ng đ n vi c m t thi t k c s d li u có ch ấ p nhậ n đ ượ c hay không. Hai s ự ki ệ n quan tr ọ ng c ầ n xét là: - Sựồạủộỉố t n t i c a m t ch s trên m ộộ t thu c tính làm tăng nhanh t ốộủ c đ c a các truy vấ n trong đó có ch ỉ ra m ộ t giá tr ị đ ố i v ớ i thu ộ c tính, và trong m ộ t số tr ườ ng h ợ p có th ể tăng t ố c đ ộ các phép n ố i liên quan đ ế n thu ộ c tính đó. - Mặ t khác, m ỗộỉốượ i m t ch s đ c xây d ự ng cho m ộộ t thu c tính c ủộ a m t quan hệ nào đ ấ y làm cho các phép chèn, xoá và c ậ p nh ậ t đ ố i v ớ i quan h ệ đó phứ c t ạ p và t ố n th ờ i gian h ơ n. Việựọỉốộ c l a ch n ch s là m t trong nh ữ ng ph ầ n khó nh ấủệếế t c a vi c thi t k cơởữệ s d li u vì nó đòi h ỏả i ph i đánh giá s ựộẫặ tr n l n đ c thù c ủ a các truy vấ n và các phép toán khác s ẽ có trên c ơ s ở d ữ li ệ u. N ế u m ộ t quan h ệ đượ c truy v ấườ n th ng xuyên h ơậậ n là c p nh t thì các ch ỉố s trên các thu ộ c tính thườ ng đ ượ c ch ỉ ra trong truy v ấ n là có ý nghĩa. Các ch ỉ s ố có l ợ i v ớ i các thuộ c tính có kh ả năng đ ượ c so sánh v ớ i các h ằ ng trong m ệ nh đ ề WHERE củ a các truy v ấ n. Các ch ỉ s ố cũng có l ợ i đ ố i v ớ i các thu ộ c tính thườ ng xu ấ t hi ệ n trong các đi ề u ki ệ n n ố i. 50
1.7 KHUNG NHÌN (VIEW) Các quan hệ đ ượ c đ ị nh nghĩa v ớ i l ệ nh CREATE TABLE t ồ n t ạ i th ự c s ự trong cơởữệ s d li u. Nh ưậệố v y, h th ng SQL l ưữ u tr các b ả ng trong m ộ t tổ ch ứ c v ậ t lý nào đó. Chúng là th ườ ng tr ự c và t ồ n t ạ i lâu dài, ch ỉ b ị thay đổ i khi th ự c hi ệ n l ệ nh INSERT ho ặ c các l ệ nh c ậ p nh ậ t. Có mộ t l ớ p các quan h ệ khác c ủ a SQL, g ọ i là các khung nhìn, không t ồ n tạ i m ộ t cách v ậ t lý. Đúng h ơ n là chúng đ ượ c đ ị nh nghĩa b ằ ng m ộ t bi ể u thứ c gi ố ng nh ư m ộ t truy v ấ n. Các khung nhìn có th ể đ ượ c truy v ấ n nh ư là chúng tồ n t ạ i m ộ t cách v ậ t lý, và trong m ộ t s ố tr ườ ng h ợ p, chúng ta có thể s ử a đ ố i các khung nhìn. 1.7.1 Khai báo các khung nhìn Dạ ng đ ơ n gi ả n nh ấ t c ủ a m ộ t đ ị nh nghĩa khung nhìn là 1. Các từ khoá CREATE VIEW, 2. Tên củ a khung nhìn, 3. Từ khoá AS và 4. Mộ t truy v ấ n Q. Truy v ấ n này là đ ị nh nghĩa c ủ a khung nhìn. M ỗ i khi chúng ta truy vấ n khung nhìn, SQL ứ ng x ử nh ư là Q đã đ ượ c th ự c hi ệ n tạ i th ờ i đi ể m đó và truy v ấ n đ ượ c áp d ụ ng đ ố i v ớ i quan h ệ do Q sinh ra. Như v ậ y, m ộ t khai báo khung nhìn đ ơ n gi ả n có d ạ ng: CREATE VIEW AS ; Vídụ 40: Giả s ử chúng ta mu ố n có m ộ t khung nhìn là m ộ t ph ầ n c ủ a quan hệ NHÂNVIÊN, ch ứ a Mãs ố NV, H ọ đ ệ m,Tên, L ươ ng và Mãs ố ĐV c ủ a các nhân viên có đị a ch ỉ là ‘Hàn ộ i’. Chúng ta có th ể đ ị nh nghĩa khung nhìn này bằ ng: 1) CREATE VIEW NVHÀNỘ I AS 2) SELECT Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV 3) FROM NHÂNVIÊN 4) WHERE Đị ach ỉ = ‘Hà n ộ i’ ; 51
Theo đị nh nghĩa này, tên c ủ a khung nhìn là NVHÀN Ộ I, các thu ộ c tính củ a khung nhìn là Mãs ố NV, H ọ đ ệ m,Tên, L ươ ng, Đ ị ach ỉ , Mãs ố ĐV. Đ ị nh nghĩa củ a khung nhìn là t ừ dòng 2 đ ế n dòng 4). 1.7.2 Truy vấ n các khung nhìn Quan hệ NVHÀN Ộ I không ch ứ a các b ộ theo nghĩa thông th ườ ng. Đúng hơ n là n ế u chúng ta truy v ấ n NVHÀN Ộ I, các b ộ thích h ợ p s ẽ nh ậ n đ ượ c từảơở b ng c s NHÂNVIÊN, vì v ậ y truy v ấ n có th ểượảờếả đ c tr l i. K t qu là chúng ta có thể h ỏ i NVHÀN Ộ I hai l ầ n cùng m ộ t truy v ấ n và nh ậ n đ ượ c các trả l ờ i khác nhau. Lý do là ở ch ỗ , m ặ c dù chúng ta không thay đ ổ i đ ị nh nghĩa củ a khung nhìn NVHÀN Ộ I nh ư ng b ả ng c ơ s ở NHÂNVIÊN có th ể bị thay đ ổ i trong th ờ i gian gi ữ a hai l ầ n truy v ấ n. Vídụ 41 Chúng ta có thể truy v ấ n khung nhìn NVHÀN Ộ I nh ư th ể nó là mộ t b ả ng đ ượ c l ư u gi ữ , ch ẳ ng h ạ n: SELECT Tên FROM NVHÀNỘ I WHERE Mãsố ĐV = 4 ; Đị nh nghĩa c ủ a khung nhìn NVHÀN Ộ I đ ượ c s ử d ụ ng đ ể bi ế n đ ổ i truy vấ n ở trên thành truy v ấ n m ớ i ch ỉ nh ắ m đ ế n b ả ng c ơ s ở NHÂNVIÊN. Truy vấ n trên t ươ ng đ ươ ng v ớ i truy v ấ n SELECT Tên FROM NHÂNVIÊN WHERE Đị ach ỉ = ‘Hà n ộ i’ AND Mãs ố ĐV = 4 ; Ví dụ 42 Có thể vi ế t các truy v ấ n ch ứ a c ả b ả ng l ẫ n khung nhìn, ch ẳ ng hạ n: SELECT TênĐV, Tên FROM NVHÀNỘƠỊ I, Đ NV WHERE NVHÀNỘ I.Mãs ố ĐV = Đ Ơ NV Ị .Mãs ố ĐV Truy vấ n này đòi h ỏ i tên c ủ a đ ơ n v ị và tên c ủ a các nhân viên có đ ị a ch ỉ t ạ i Hà nộ i. 52
Vídụ 43 Chúng ta hãy xét mộ t truy v ấ n ph ứ c t ạ p h ơ n đ ượ c s ử d ụ ng đ ể đị nh nghĩa m ộ t khung nhìn. CREATE VIEW NVĐV AS SELECT TênĐV, Tên FROM NHÂNVIÊN, ĐƠỊ NV WHERE NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV; Chúng ta có thể truy v ấ n khung nhìn này nh ư th ể nó là m ộ t quan h ệ đ ượ c lư u tr ữ , ví d ụ SELECT Tên FROM NVĐV WHERE Tên = ‘Thanh’; Truy vấ n ở trên t ươ ng đ ươ ng v ớ i truy v ấ n: SELECT Tên FROM NHÂNVIÊN, ĐƠỊ NV WHERE (NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV) AND (Tên = ‘Thanh’); 1.7.3 Đặ t tên l ạ i các thu ộ c tính Đôi khi chúng ta thích đặ t tên m ớ i cho các thu ộ c tính c ủ a khung nhìn. Đ ể làm điề u đó, chúng ta ch ỉ ra các thu ộ c tính c ủ a khung nhìn b ằ ng cách li ệ t kê chúng ở trong c ặ p d ấ u ngo ặ c và đ ặ t ở sau tên c ủ a khung nhìn trong lệ nh CREATE VIEW. Ví d ụ , chúng ta có th ể vi ế t l ạ i đ ị nh nghĩa khung nhìn ở ví d ụ 1.43 nh ư sau: CREATE VIEW NVĐV(Tênđơ nv ị , Tênnhânviên) AS SELECT TênĐV, Tên FROM NHÂNVIÊN, ĐƠỊ NV WHERE NHÂNVIÊN.Mãsố ĐV = Đ Ơ NV Ị .Mãs ố ĐV; 53
Hai khung nhìn là hoàn toàn như nhau nh ư ng các thu ộ c tính đ ượ c đ ặ t tên lạ i, Tênđ ơ nv ị và Tênnhânviên thay cho TênĐV và Tên. 1.7.4 Sử a đ ổ i các khung nhìn Trong nhiề u tr ườ ng h ợ p chúng ta không th ể th ự c hi ệ n m ộ t l ệ nh chèn, xoá hoặ c c ậ p nh ậ t đ ố i v ớ i m ộ t khung nhìn b ở i vì khung nhìn không gi ố ng nh ư mộảơở t b ng c s . Tuy nhiên, đ ốớ i v i các khung nhìn t ươốơả ng đ i đ n gi n, gọ i là các khung nhìn c ậ p nh ậ t đ ượ c, có kh ả năng chuy ể n đ ổ i c ậ p nh ậ t củ a khung nhìn thành m ộậậươươ t c p nh t t ng đ ng trên m ộảơở t b ng c s và phép cậ p nh ậ t có th ểượựệốớả đ c th c hi n đ i v i b ng c ơởềệể s . Đi u ki n đ khung nhìn cậ p nh ậ t đ ượ c là các khung nhìn đ ượ c đ ị nh nghĩa b ằ ng phép chọ n m ộ t s ố thu ộ c tính t ừ m ộ t quan h ệ R (quan h ệ này cũng có th ể là m ộ t khung nhìn cậ p nh ậ t đ ượ c). Hai đi ể m k ỹ thu ậ t quan tr ọ ng: • Mệ nh đ ề WHERE không đ ượ c bao hàm R trong m ộ t truy v ấ n con • Danh sách trong mệ nh đ ề SELECT ph ả i ch ứ a đ ủ các thu ộ c tính sao cho vớ i m ỗ i b ộ đ ượ c chèn vào khung nhìn, chúng ta có th ể đi ề n các thuộ c tính khác vào v ớ i các giá tr ị null ho ặ c ng ầ m đ ị nh thích hợ p và có m ộộủ t b c a quan h ệơởẽượạ c s s đ c t o nên t ừộượ b đ c chèn vào củ a khung nhìn. Ví dụ 44 Giả s ử chúng ta c ố g ắ ng chèn vào khung nhìn NVHÀN Ộ I m ộ t bộ : INSERT INTO NVHÀNỘ I VALUES (‘NV065’, ‘Nguyễ n Đình’, ‘Thi’, 4500, 4); Khung nhìn NVHÀNỘ I h ầ u nh ư tho ả mãn các đi ề u ki ệ n c ậ p nh ậ t đ ượ c củ a SQL b ở i vì khung nhìn ch ỉ yêu c ầ u m ộ t s ố thành ph ầ n c ủ a các b ộ c ủ a bảơở ng c s NHÂNVIÊN. Ch ỉộấề có m t v n đ là vì thu ộ c tính Đ ịỉủ ach c a bả ng NHÂNVIÊN không ph ả i là m ộ t thu ộ c tính c ủ a khung nhìn, b ộ giá tr ị mà chúng ta chèn vào NHÂNVIÊN sẽ có giá tr ị NULL ch ứ không ph ả i là ‘Hà nộ i’ nh ư là giá tr ị c ủ a nó cho Đ ị ach ỉ . B ộ giá tr ị này không tho ả mãn điề u ki ệ n là đ ị a ch ỉ c ủ a nhân viên là Hà n ộ i. 54
Như v ậ y, đ ể làm cho khung nhìn NVHÀN Ộ I c ậ p nh ậ t đ ượ c, chúng ta s ẽ thêm thuộ c tính Đ ị ach ỉ cho m ệ nh đ ề SELECT c ủ a nó, m ặ c dù rõ ràng là đị a ch ỉ nhân viên là Hà n ộ i. Đ ị nh nghĩa l ạ i c ủ a khung nhìn NVHÀN Ộ I là: 1) CREATE VIEW NVHÀNỘ I AS 2) SELECT Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Đ ị ach ỉ ,Mãs ố ĐV 3) FROM NHÂNVIÊN 4) WHERE Đị ach ỉ = ‘Hà n ộ i’ ; Sau đó, chúng ta viế t l ệ nh chèn vào khung nhìn c ậ p nh ậ t đ ượ cNVHÀN Ộ I như sau: INSERT INTO NVHÀNỘ I VALUES (‘NV065’, ‘Nguyễ n Đình’, ‘Thi’, 4500, 4); Thự c hi ệ n l ệ nh chèn, chúng ta t ạ o ra m ộ t b ộ c ủ a NHÂNVIÊN sinh t ừ mộ t b ộ c ủ a khung nhìn đ ượ c chèn vào khi đ ị nh nghĩa khung nhìn đ ượ c áp dụ ng cho NHÂNVIÊN. Các thu ộ c tính khác không xu ấ t hi ệ n trong khung nhìn chắ c ch ắ n t ồ n t ạ i trong b ộ NHÂNVIÊN đ ượ c chèn vào. Tuy nhiên chúng ta không thể suy ra giá tr ị c ủ a chúng. Trong k ế t qu ả b ộ m ớ i c ủ a NHÂNVIÊN phả i có trong các thành ph ầ n đ ố i v ớ i m ỗ i thu ộ c tính này các giá trị m ặ c đ ị nh thích h ợ p ho ặ c NULL ho ặ c m ộ t ng ầ m đ ị nh nào đó đã đượ c khai báo cho thu ộ c tính. Chúng ta cũng có thể lo ạ i b ỏ ra kh ỏ i m ộ t khung nhìn c ậ p nh ậ t đ ượ c. Lệ nh xoá, cũng nh ư l ệ nh chèn, đ ượ c th ự c hi ệ n thông qua m ộ t quan h ệ nề n R và gây ra vi ệạỏộộủ c lo i b m t b c a R gây ra b ộượ đ c xoá c ủ a khung nhìn. Vídụ 45: Giả s ử chúng ta mu ố n xoá kh ỏ i khung nhìn c ậ p nh ậ t đ ượ c NVHÀNỘ I t ấ t c ả các b ộ có tên ch ứ a t ừ ‘an’. Ta có th ể vi ế t l ệ nh xoá nh ư sau: DELETE FROM NVHÀNỘ I WHERE Tên LIKE “%an%” ; 55
Lệ nh xoá này đ ượ c chuy ể n thành m ộệ t l nh xoá t ươươ ng đ ng trên b ảơ ng c sở NHÂNVIÊN; ch ỉ khác ở ch ỗ là đi ề u ki ệ n đ ị nh nghĩa khung nhìn NVHÀNỘ I đ ượ c thêm vào các đi ề u ki ệ n c ủ a m ệ nh đ ề WHERE. K ế t qu ả là lệ nh xoá nh ư sau: DELETE FROM NHÂNVIÊN WHERE Tên LIKE “%an%” AND Đị ach ỉ = ‘Hà n ộ i’; Tươựộệậậ ng t , m t l nh c p nh t trên m ộ t khung nhìn c ậậượượ p nh t đ c đ c thự c hi ệ n thông qua quan h ệ n ề n. Nh ư v ậ y l ệ nh c ậ p nh ậ t khung nhìn có tác dụậậấả ng c p nh t t t c các b ộủ c a quan h ệề n n sinh ra các b ộượậ đ c c p nhậ t trong khung nhìn. Ví dụ 46: Lệ nh c ậ p nh ậ t khung nhìn UPDATE NVHÀNỘ I SET Lươ ng = 4500 WHERE Mãsố NV = ‘NV002’; đượ c chuy ể n thành l ệ nh c ậ p nh ậ t b ả ng c ơ s ở : UPDATE NHÂNVIÊN SET Lươ ng = 4500 WHERE Mãsố NV = ‘NV002’ AND Đ ị ach ỉ = ‘Hà n ộ i’; Loạ i c ậ p nh ậ t m ộ t khung nhìn cu ố i cùng là lo ạ i b ỏ nó. L ệ nh c ậ p nh ậ t này có thể th ự c hi ệ n dù khung nhìn có c ậ p nh ậ t đ ượ c hay không. L ệ nh DROP thông thườ ng là: DROP VIEW NVHÀNỘ I ; Chú ý rằ ng l ệ nh này xoá đ ị nh nghĩa c ủ a khung nhìn vì v ậ y chúng ta không thể ti ế p t ụ c truy v ấ n ho ặ c c ậ p nh ậ t đ ố i v ớ i khung nhìn này n ữ a. Tuy nhiên, việ c xoá b ỏ m ộ t khung nhìn không làm ả nh h ưở ng đ ế n m ộ t b ộ nào củ a quan h ệ n ề n NHÂNVIÊN. Ng ượ c l ạ i DROP TABLE Movie 56
sẽ không ch ỉ xoá b ỏ b ả ng NHÂNVIÊN, nó cũng s ẽ làm cho khung nhìn NVHÀNỘ I tr ở nên vô d ụ ng b ở i vì m ộ t truy v ấ n s ử d ụ ng nó s ẽ tham chi ế u đế n m ộ t quan h ệ NHÂNVIÊN không t ồ n t ạ i. 1.7.5 Giả i thích các truy v ấ n có ch ứ a các khung nhìn Để gi ả i thích các truy v ấ n khung nhìn, chúng ta l ầ n theo cách m ộ t truy vấ n có ch ứ a khung nhìn đ ượ c x ử lý nh ư th ế nào. Tưưởơảượ t ng c b n đ c minh ho ạở hình v ẽướ d i đây (hình 3). M ộ t truy vấượểịằ n Q đ c bi u th b ng cây bi ểứ u th c trong đ ạố i s quan h ệ . Cây bi ể u thứ c này s ử d ụ ng các quan h ệ là các khung nhìn làm lá. Trong hình v ẽ cây có hai lá, đó là các khung nhìn V và W. Để gi ả i thích Q theo thu ậ t ng ữ c ủ a các bả ng c ơ s ở , chúng ta tìm các đ ị nh nghĩa c ủ a V và W. Các đ ị nh nghĩa này cũng đượ c bi ể u th ị b ằ ng các cây bi ể u th ứ c trong đ ạ i s ố quan h ệ . Trong hình 3 (ở bên ph ả i) chúng ta thay các lá V và W b ằ ng các đ ị nh nghĩa củ a các khung nhìn đó. Cây k ế t qu ả là m ộ t truy v ấ n trên các b ả ng c ơ s ở tươ ng đ ươ ng v ớ i truy v ấ n g ố c trên các khung nhìn. Q V W Hình 3: Thay thế các đ ị nh nghĩa khung nhìn cho các tham chi ế u khung nhìn Ví dụ 47: Ta xét khung nhìn và truy vấ n đ ượ c đ ị nh nghĩa nh ư sau: 1) CREATE VIEW NVHÀNỘ I AS 2) SELECT Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV 3) FROM NHÂNVIÊN 4) WHERE Đị ach ỉ = ‘Hà n ộ i’ ; 57
Mộ t cây bi ể u th ứ c cho truy v ấ n đ ị nh nghĩa khung nhìn này đ ượ c ch ỉ ra ở hình 4. π Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV σ Đị ach ỉ = ‘Hàn ộ i’ NHÂNVIÊN Hình 4: Cây biể u th ứ c cho khung nhìn NVHÀN Ộ I Truy vấ n ở ví d ụ 41 có d ạ ng SELECT Tên FROM NVHÀNỘ I WHERE Mãsố ĐV = 4 ; Cây biể u th ứ c cho truy v ấ n này đ ượ c ch ỉ ra ở hình 5 π Tên σMã số ĐV = 4 NVHÀNỘ I Hình 5: Cây biể u th ứ c cho truy v ấ n ở ví d ụ 41 58
Chú ý rằ ng lá c ủ a cây này bi ể u di ễ n khung nhìn NVHÀN Ộ I. T ừ đó, chúng ta giả i thích truy v ấ n b ằ ng cách thay th ế cây truy v ấ n c ủ a NVHÀNỘ I vào v ị trí c ủ a NVHÀN Ộ I trong cây bi ể u th ứ c c ủ a truy v ấ n. Kế t qu ả , chúng ta có cây bi ể u th ứ c nh ư sau: π Tên σMã số ĐV = 4 π Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV σ Đị ach ỉ = ‘Hàn ộ i’ NHÂNVIÊN Cây truy vấ n này là m ộ t gi ả i thích ch ấ p nh ậ n đ ượ c c ủ a truy v ấ n. Tuy nhiên nó đượễạằ c di n đ t b ng cách ph ứạ c t p không c ầếệố n thi t. H th ng SQL sẽ áp d ụ ng các bi ế n đ ổ i đ ố i v ớ i cây này đ ể làm cho nó gi ố ng nh ư biể u th ứ c cây đ ố i v ớ i truy v ấ n SELECT Tên FROM NHÂNVIÊN WHERE Đị ach ỉ = ‘Hàn ộ i’ AND Mãs ố ĐV = 4 ; Chẳ ng h ạ n, chúng ta có th ể đ ư a phép chi ế u π Mãsố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV lên trên phép chọ n σMã số ĐV = 4 vì việ c thay đ ổ i đó không làm ả nh h ưở ng đ ế n kế t qu ả c ủ a bi ể u th ứ c. Sau đó, chúng ta có hai phép chi ế u liên ti ế p, đ ầ u tiên chiế u trên Mãs ố NV, H ọ đ ệ m,Tên, L ươ ng, Mãs ố ĐV, sau đó chi ế u trên Tên. Rõ ràng lầ n chi ế u th ứ nh ấ t là th ừ a, chúng ta có th ể lo ạ i b ỏ nó. Nh ư vậ y chúng ta có th ể thay th ế hai phép chi ế u b ằ ng b ằ ng m ộ t phép chi ế u trên Tên. Hai phép chọ n cũng có th ể đ ượ c k ế t h ợ p l ạ i. Nói chung, có th ể thay thế hai phép ch ọ n liên ti ế p b ằ ng m ộ t phép ch ọ n v ớ i phép toán AND củ a các đi ề u ki ệ n c ủ a chúng. Cây bi ể u th ứ c k ế t qu ả là: π Tên 59
σMã số ĐV = 4 AND Đị ach ỉ = ‘Hàn ộ i’ NHÂNVIÊN Đó là cây nhậ n đ ượ c t ừ truy v ấ n: SELECT Tên FROM NHÂNVIÊN WHERE Đị ach ỉ = ‘Hà n ộ i’; 1.8 TỔ NG K Ế T CH ƯƠ NG I 1- SQL: Ngôn ngữ SQL là ngôn ng ữ truy v ấ n chính cho các h ệ c ơ s ở d ữ liệ u quan h ệ . Chu ẩ n hi ệ n t ạ i đ ượ c g ọ i là SQL-99 ho ặ c SQL3 2- Các truy vấ n Select-From-Where: D ạ ng truy v ấ n SQL ph ổ bi ế n nh ấ t có dạ ng select-from-where. Nó cho phép ta l ấ y tích c ủ a nhi ề u quan h ệ (mệề nh đ FROM), áp d ụộềệốớ ng m t đi u ki n đ i v i các b ộủếả c a k t qu (mệ nh đ ề WHERE) và sinh ra các thành ph ầ n mong mu ố n (m ệ nh đ ề SELECT). 3- Truy vấ n con: Các truy v ấ n Select-From-Where cũng có th ể đ ượ c s ử dụ ng nh ư các truy v ấ n con bên trong m ộ t m ệ nh đ ề WHERE ho ặ c mệ nh đ ề FROM c ủ a m ộ t truy v ấ n khác. Các phép toán EXIST, IN, ALL, và ANY coa thể đ ượ c s ử d ụ ng đ ể di ễ n đ ạ t các đi ề u ki ệ n có giá trị Lôgic v ề các quan h ệ là k ế t qu ả c ủ a m ộ t truy v ấ n con trong m ệ nh đề WHERE. 4- Các phép toán tậ p h ợ p trên các quan h ệ : Chúng ta có th ể l ấ y h ợ p, giao, hoặ c tr ừ c ủ a các quan h ệ b ằ ng cách n ố i các quan h ệ ho ặ c n ố i các truy vấ n xác đ ị nh các quan h ệ , v ớ i các t ừ khóa UNION, INTESECT và EXCEPT tươ ng ứ ng. 5- Các biể u th ứ c n ố i: SQL có các phép toán nh ư NATURAL JOIN có th ể áp dụ ng cho các quan h ệ nh ư các truy v ấ n ho ặ c đ ể xác đ ị nh các quan hệ trong m ộ t m ệ nh đ ề FROM. 60
6- Các giá trị Null: SQL cung c ấ p m ộ t giá tr ị NULL đ ặ c bi ệ t, nó xu ấ t hiệ n trong các thành ph ầ n c ủ a các b ộ không có giá tr ị c ụ th ể cho chúng. Các phép toán số h ọ c và logic không dùng đ ượ c v ớ i NULL. Việ c so sánh m ộ t giá tr ị b ấ t kỳ v ớ i NULL, th ậ m chí giá tr ị so sánh là NULL, sẽ cho giá tr ị UNKNOWN. Giá tr ị UNKNOWN đ ố i x ử trong các biể u th ứ c có giá tr ị logic nh ư là n ử a đ ườ ng gi ữ a TRUE và FALSE. 7- Nố i ngoài: SQL cung c ấ p m ộ t phép toán OUTER JOIN. Phép toán này nố i các quan h ệạếảứ và t o ra k t qu có ch a các b ộừộặả treo t m t ho c c hai quan hệ tham gia phép n ố i. Các b ộ treo đ ượ c đ ộ n thêm các giá tr ị NULL ở trong k ế t qu ả . 8- Mô hình túi (bag) củ a các quan h ệ : SQL xem các quan h ệ nh ư các túi các bộ ch ứ không ph ả i t ậ p h ợ p các b ộ . Chúng ta có th ể ép bu ộ c vi ệ c loạ i b ỏ các b ộ trùng l ặ p b ằ ng t ừ khóa DISTINCT, trong khi đó t ừ khóa ALL cho phép kế t qu ả là m ộ t túi trong hoàn c ả nh các túi không phả i là ng ầ m đ ị nh. 9- Phép nhóm: Các giá trị xu ấ t hi ệ n trong m ộ t c ộ t c ủ a quan h ệ có th ể đượ c tính t ổếợạằ ng (k t h p l i) b ng cách s ửụộ d ng m t trong các t ừ khóa SUM, AVG, MIN, MAX hoặ c COUNT. Các b ộ có th ể đ ượ c phân nhóm trướ c đ ể k ế t h ợ p v ớ i các t ừ khóa GROUP BY. M ộ t s ố nhóm có thể b ị lo ạ i b ỏ v ớ i m ộ t m ệ nh đ ề HAVING. 10- Các lệ nh c ậ p nh ậ t: SQL cho phép chúng ta làm thay đ ổ i các b ộ trong mộ t quan h ệ . Chúng ta có th ể INSERT (chèn các b ộ m ớ i), DELETE (xóa các bộ ), UPDATE(thay đ ổ i m ộ t s ố b ộ đã t ồ n t ạ i) b ằ ng cách vi ế t các lệ nh SQL s ử d ụ ng các t ừ khóa đó. 11- Đị nh nghĩa d ữ li ệ u: SQL có các l ệ nh mô t ả các ph ầ n t ử c ủ a m ộ t lượ c đ ồ c ơ s ở d ữ li ệ u. L ệ nh CREATE TABLE cho phép chúng ta mô tảượồ l c đ cho các quan h ệượưữọ đ c l u gi (g i là các b ả ng), ch ỉ ra các thuộ c tính và ki ể u c ủ a chúng và các giá tr ị ng ầ m đ ị nh. 12- Sửổ a đ i các l ượồ c đ : Chúng ta có th ể thay đ ổệạủộ i di n m o c a m t lượồơởữệằộệ c đ c s d li u b ng m t l nh ALTER. Các thay đ ổ i này bao gồ m vi ệ c thêm và lo ạ i b ỏ các thu ộ c tính các l ượ c đ ồ quan h ệ và thay 61
đổ i các giá tr ịầị ng m đ nh liên k ếớộộ t v i m t thu c tính ho ặộề c m t mi n. Chúng ta cũng có thể s ử d ụ ng l ệ nh DROP đ ể lo ạ i b ỏ hoàn toàn các quan hệ ho ặ c các thành ph ầ n l ượ c đ ồ khác. 13- Các chỉ s ố : Trong khi không ph ả i là m ộ t ph ầ n c ủ a chu ẩ n SQL, các hệ th ố ng SQL th ươ ng m ạ i cho phép mô t ả các ch ỉ s ố trên các thu ộ c tính; các chỉ s ố này là tăng t ố c đ ộ m ộ t s ố truy v ấ n và c ậ p nh ậ t ch ứ a đặảủộ c t c a m t giá tr ịốớ đ i v i thu ộ c tính đ ượỉố c ch s hóa. 14- Khung nhìn: Mộ t khung nhìn là m ộ t đ ị nh nghĩa v ề vi ệ c m ộ t quan hệ (khung nhìn) đ ượ c xây d ựừảượưữ ng t các b ng đ c l u gi trong c ơ sở d ữ li ệ u nh ư th ế nào. Các khung nhìn có th ể đ ượ c truy v ấ n gi ố ng như chúng là các b ảượưữ ng đ c l u gi , và m ộệố t h th ng SQL s ửổ a đ i các truy vấ n v ề m ộ t khung nhìn sao cho truy v ấ n đ ượ c thay th ế thành truy vấ n trên b ả ng c ơ s ở đã đ ượ c s ử d ụ ng đ ể đ ị nh nghĩa khung nhìn. MỘỐẬ T S BÀI T P Giả s ử chúng ta có c ơ s ở d ữ li ệ u sau(xem ở PH Ụ L Ụ C 2): Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) I. Viế t các truy v ấ n sau: 1) Tìm số model, t ố cđ ộ , và kích c ỡ đĩa c ứ ng c ủ a t ấ t c ả các máy PC có giá thấ p h ơ n $1200. 2) Làm như câu 1) nh ư ng thay tên c ộ t speed b ằ ng megahertz và c ộ t hd bằ ng gigabyte 3) Tìm các nhà sả n xu ấ t các máy in 4) Tìm số model, kích c ỡ b ộ nh ớ và kích c ỡ màn hình c ủ a các máy xách tay (Laptop) có giá trên $2000 62
5) Đư a ra t ấ t c ả các b ộ trong quan h ệ Printer v ớ i các máy in màu. Chú ý rằ ng color là thu ộ c tính có giá tr ị lôgic 6) Đư a ra s ố model, t ố c đ ộ và kích c ỡ c ủ a đĩa c ứ ng đ ố i v ớ i các máy PC coa đĩa DVD 12X hoặ c 16X và giá th ấ p h ơ n $2000. Bán có thể xem thu ộ c tính rd nh ư là thu ộ c tính có giá tr ị là chu ỗ i ký tự . II Viế t các truy v ấ n 1) Đư a ra nhà s ả n xu ấ t và t ố c đ ộ c ủ a các Laptop v ớ i đĩa c ứ ng ít nhấ t là 30 gigabyte. 2) Đư a ra s ố model và giá c ủ a t ấ t c ả các s ả n ph ẩ m (v ớ i m ọ i ki ể u) do nhà sả n xu ấ t B làm ra 3) Đư a ra nh ữ ng nhà s ả n xu ấ t bán các Laptop nh ư ng không bán các máy PC 4) Tìm các kích cỡ đĩa c ứ ng có trong hai ho ặ c nhi ề u h ơ n PC 5) Đư a ra các c ặ p model PC có cùng t ố c đ ộ và b ộ nh ớ (ram). M ộ t cặ p ch ỉ đ ượ c li ệ t kê m ộ t l ầ n nghĩa là li ệ t kê (i,j) nh ư ng không liệ t kê (j,i). 6) Đư a ra các nhà s ả n xu ấ t s ả n xu ấ t ít nh ấ t là hai lo ạ i máy tính khác nhau (PC hoặ c Laptop) v ớ i t ố c đ ộ ít nh ấ t là 1000. III.Trong phầ n bài t ậ p này, b ạ n ph ả i s ử d ụ ng ít nh ấ t m ộ t truy v ấ n con trong câu trả l ờ i và vi ế t m ỗ i truy v ấ n trong hai cách khác nhau. 1) Đư a ra nh ữ ng ng ườ i s ả n xu ấ t các PC có t ố c đ ộ ít nh ấ t là 1200. 2) Đư a ra các máy in có giá cao nh ấ t. 3) Đư a ra các máy xách tay có t ố c đ ộ th ấ p h ơ n t ố c đ ộ c ủ a các PC 4) Đư a ra s ố model c ủ a các m ụ c (PC, laptop, printer) v ớ i giá cao nh ấ t. 5) Đư a ra nhà s ả n xu ấ t c ủ a máy in màu có giá th ấ p nh ấ t. 6) Đư a ra các nhà s ả n xu ấ t c ủ a các PC có b ộ x ử lý nhanh nh ấ t trong số các PC có dung l ượ ng RAM bé nh ấ t. 63