Giáo trình nhập môn Cơ sở dữ liệu - Chương 3: Các ngôn ngữ dữ liệu đối với mô hình quan hệ - Vũ Tuyết Trinh

Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 1 Các ngôn ngữ dữ liệu đối với mô hình quan hệ Vũ Tuyết Trinh trinhvt@it-hut.edu.vn Bộ môn Các hệ thống thông tin, Khoa Công nghệ thông tin Đại học Bách Khoa Hà Nội Ứng dụngHệ CSDL Hệ QTCSDL - Định nghĩa DL - Thao tác DL 2 CSDL CSDL Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 2 Nội dung { Các cách tiếp cận đối với thiết kế ngôn ngữ của CSDL quan hệ z Giới thiệu một số ngôn ngữ và phân loại ¾ So sánh và đánh giá { Một số ngôn ngữ dữ liệu mức cao z QBE (Query By Example) z SQL (Structured Query Language) 3 { Kết luận Đặt vấn đề { Mục đích của ngôn ngữ dữ liệu { Tại sao có nhiều ngôn ngôn ngữ dữ liệu? { Ngôn ngữ cấp thấp vs. Ngôn ngữ cấp cao? 4 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 3 Ví dụ { Tìm tên của các sinh ố Student viên nào s ng ở Bundoora z Tìm các bộ của bảng Student có Suburb = Bundoora z Đưa ra các giá trị của thuộc tính Name của Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn 5 các bộ này Ví dụ (2) { Tìm các sinh viên Id Name Suburb 1108 Robert Kew 3936 Gl d Student đăng ký khoá học có mã số 113 z Tìm các giá trị SID trong bảng Enrol có Course tương ứng là 113 z Đưa các bộ của bảng en Bun oora 8507 Norman Bundoora 8452 Mary Balwyn SID Course 3936 101 1108 113 Enrol 6 Student có SID trong các giá trị tìm thấy ở trên 8507 101 No Name Dept 113 BCS CSCE 101 MCS CSCE Course Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 4 Phân loại các ngôn ngữ truy vấn { Ngôn ngữ đại số z 1 câu hỏi = 1 tập các phép toán trên các quan hệ z Được biểu diễn bởi một biểu thức đại số (quan hệ) { Ngôn ngữ tính toán vị từ z 1 câu hỏi = 1 mô tả của các bộ mong muốn z Được đặc tả bởi một vị từ mà các bộ phải thoả mãn z Phân biệt 2 lớp: 7 { ngôn ngữ tính toán vị từ biến bộ { ngôn ngữ tính toán vị từ biến miền Ngôn ngữ đại số quan hệ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 5 Tổng quan { Gồm các phép toán tương ứng với các thao tác trên các quan hệ { Mỗi phép toán z Đầu vào: một hay nhiều quan hệ z Đầu ra: một quan hệ { Biểu thức đại số quan hệ = chuỗi các phép toán { Kết quả thực hiện một biểu thức đại số là một 9 quan hệ { Được cài đặt trong phần lớn các hệ CSDL hiện nay Phân loại các phép toán { Phép toán quan hệ Phé hiế ( j ti )z p c u pro ec on z Phép chọn (selection) z Phép kết nối (join) z Phép chia (division) { Phép toán tập hợp z Phép hợp (union) z Phép giao (intersection) Phé t ừ (diff ) 10 z p r erence z Phép tích đề-các (cartesian product) Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 6 Phép chiếu { Đ/n: Lựa chọn một số thuộc tính từ một quan hệ. { Cú pháp: ™ Ví dụ: đưa ra danh sách tên của tất cả các sinh viên C1 C2 C5C4C3 C2 C5 ∏ )(,...2,1 RAA ∏ )(St d t 11 Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student Name Robert Glen Norman Mary Kết quả u enname Phép chọn { Đ/n: Lựa chọn các bộ trong một quan hệ thoả mãn ềđi u kiện cho trước. { Cú pháp: ™ Ví dụ: đưa ra danh sách những sinh viên sống ở B d R1 R2 R3 R4 R2 R3 )(Rcondition><σ 12 un oora Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student Id Name Suburb 3936 Glen Bundoora 8507 Norman Bundoora Kết quả )(" StudentBundoorasuburb=σ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 7 Vi dụ - chọn và chiếu { đưa ra tên của các sinh viên sống ở Bundoora ∏ = )( " StudentBundoorasuburbname σ Id Name Suburb Student Kết quả 13 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Name Glen Norman Phép kết nối { Đ/n: ghép các bộ từ 2 quan hệ thoả mãn điều ế ốkiện k t n i { Cú pháp: ™ Ví dụ: đưa ra danh sách các sinh viên và khoá h 2_1 RR conditionjoin >< a r b r x x a r b r c v r x s y t z E lSt d t r r x x 14 ọc SID Id Name Suburb Course 1108 1108 Robert Kew 113 3936 3936 Glen Bundoora 101 8507 8507 Norman Bundoora 101 Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student SID Course 3936 101 1108 113 8507 101 Enrol Id=SID Kết quả nrou en SIDId=>< Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 8 Ví dụ - chọn, chiếu và kết nối { đưa ra tên của các sinh viên sống ở Bundoora ))(( ", EnrolStudent SIDIdBundoosuburbCoursename ==∏ ><σ Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora và mã khoá học mà sinh viên đó đăng ký Student Name Course Kết quả 15 8507 Norman Bundoora 8452 Mary Balwyn SID Course 3936 101 1108 113 8507 101 Enrol Glen 101 Norman 101 Phép kết nối tự nhiên { Đ/n: là phép kết nối với điều kiện bằng trên các thuộc tính trùng tên ™ Ví dụ: SID SNO 1108 21 1108 23 SID Course 3936 101 1108 113 Takes Enrol * 16 8507 23 8507 29 8507 101 SID SNO Course 1108 21 113 1108 23 113 8507 23 101 8507 29 101 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 9 Phép kết nối ngoài { Phép kết nối ngoài trái { Phép kết nối ngoài phải a r b r c v x x null a r b r c v r x s y t z 17 a r b r null s x x y a r b r c v r x s y t z null t z Ví dụ về phép kết nối ngoài { Đưa ra danh sách mã số các sinh viên và mã ếkhoá học mà sinh viên đó đăng ký n u có ID Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student SID Course 3936 101 1108 113 8507 101 Enrol 18 ID Name Suburb Course 1108 Robert Kew 113 3936 Glen Bundoora 101 8507 Norman Bundoora 101 8452 Mary Balwyn null Kết quả Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 10 Phép chia { Đ/n: cho R1 và R2 lần lượt là các quan hệ n và m ếngôi. K t quả của phép chia R1 cho R2 là một quan hệ (n-m) ngôi { Cú pháp: R1:R2 Ví d axz a a a b c x y z x y : 19 ™ ụ: : Name Course Systems BCS Database BCS Database MCS Algebra MCS Subject Course BCS MCS Course Name Database Kết quả Phép hợp { Đ/n: gồm các bộ thuộc ít nhất một trong hai quan ầhệ đ u vào z 2 quan hệ khả hợp được xác định trên cùng miền giá trị { Cú pháp: R1∪ R2 Ví d R1 R2 R1 R2∪ Name CourseKết quả 20 ™ ụ: Name Course Systems BCS Database BCS Database MCS Algebra MCS Subject Name Course DataMining MCS Writing BCS Subject2 ∪ Systems BCS Database BCS Database MCS Algebra MCS DataMinin g MCS Writing BCS Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 11 Phép giao { Đ/n: gồm các bộ thuộc cả hai quan hệ đầu vào { Cú pháp: R1∩ R2 ™ Ví dụ: R1 R2 R1 R2 ∩ 21 Name Course Systems BCS Database BCS Database MCS Algebra MCS Subject Name Course DataMining MCS Database MCS Systems BCS Writing BCS Subject2 ∩ Name CourseSystems BCS Database MCS Kết quả Phép trừ { Đ/n: gồm các bộ thuộc quan hệ thứ nhất nhưng không thuộc quan hệ thứ hai z 2 quan hệ phải là khả hợp { Cú pháp: R1 \ R2 Ví d R1 R2 R1 R2 \ 22 ™ ụ: Name Course Systems BCS Database BCS Database MCS Algebra MCS Subject Name Course DataMining MCS Database MCS Systems BCS Writing BCS Subject2 Name Course Database BCS Algebra MCS Kết quả \ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 12 Phép tích đề-các { Đ/n: là kết nối giữa từng bộ của quan hệ thứ ấnh t và mỗi bộ của quan hệ thứ hai { Cú pháp: R1 x R2 a b c d x yX a b a b c x y x y x 23 c d d x y y Ví dụ phép tích đề-các Id Name Suburb Student Sport 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn SportID Sport 05 Swimming 09 Dancing X Id Name Suburb SportID Sport 1108 Robert Kew 05 Swimming Student_Sport 24 3936 Glen Bundoora 05 Swimming 8507 Norman Bundoora 05 Swimming 8452 Mary Balwyn 05 Swimming 1108 Robert Kew 09 Dancing 3936 Glen Bundoora 09 Dancing 8507 Norman Bundoora 09 Dancing 8452 Mary Balwyn 09 Dancing Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 13 Bài tập { Biểu diễn câu hỏi truy vấn bằng ngôn ngữ đại ốs quan hệ { Tính kết quả của biểu thức 25 Ngôn ngữ tính toán vị từ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 14 Tổng quan { Ứng dụng logic toán vào CSDL Nhắ l i ề l i t á{ c ạ v og c o n z Biểu thức logic: nhận 1 trong 2 giá trị ĐÚNG hoặc SAI z Biến: 1 đại lượng biến thiên trong 1 miền giá trị z Hằng: 1 đại lượng không đổi z Hàm: 1 ánh xạ từ 1 miền giá trị vào tập hợp gồm 2 giá trị hoặc đúng, hoặc sai z Vị từ: là 1 biểu thức được xây dựng dựa trên b/t logic 27 z Phép toán logic: phủ định (¬) kéo theo (⇒), và (∧) hoặc(∨) z Lượng từ: với mọi (∀), tồn tại(∃) { Phân loại: z Phép tính vị từ biến bộ z Phép tính vị từ biến miền Phép tính vị từ biến bộ { Đ/n: là ngôn ngữ vị từ có biến là các bộ { Định nghĩa hình thức { t | P(t) } z t : tập các bộ kết quả sao cho vị từ P là đúng đối với t z P: là một biểu thức có duy nhất 1 biến tự do t { Một số quy ước: t[A] iá t ị ủ bộ t t i th ộ tí h A 28 z : g r c a ạ u c n z t[X]: giá trị của bộ t trên tập các thuộc tính X z t∈ R: bộ t là một bộ trong quan hệ R Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 15 Biểu thức nguyên tố { t ∈ R ếzt là một bi n bộ zR là một quan hệ (không sử dụng phép toán ∉) { t[x] θ u[y] zt và u là các biến bộ zx và y lần lượt là 1 thuộc tính mà trên đó t và u được xác định zθ là một phép toán so sánh ( ≤ ≠ ≥) 29 , , , , , ™ Ví dụ: s ∈ Student e ∈ Enrol s[Id] = e[SID] Biểu thức nguyên tố (2) { t[x] θ c ếzt là một bi n bộ zx là một thuộc tính mà trên đó u xác định zθ là một phép so sánh zc là một hằng trong miền của thuộc tính x. ™ Ví dụ s[Suburb] = ‘‘Bundoora’’ 30 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 16 Biểu thức tổng quát { Một biểu thức nguyên tố là một biểu thức. { P1 là biểu thức ⇒ ¬P1, (P1) là các biểu thức { P1 và P2 là biểu thức ⇒ P1 ∧ P2 , P1 ∨ P2 , P1 ⇒ P2 là các biểu thức 31 { P1 là biểu thức chứa 1 biến bộ tự do u, và R là 1 quan hệ ⇒ ∃ u ∈ r (P1(u)), ∀ u ∈ r (P1(u)) cũng là các biểu thức Các phép biến đổi tương đương { P1 ∧ P2 ⇔ ¬(¬P1 ∨ ¬P2) { t ∈ r (P1(t)) ⇔ ¬∃ t∈r (¬P1(t)) { P1 ⇒ P2 ⇔ ¬P1 ∨ P2 32 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 17 Bài tập { Biểu diễn câu hỏi bằng ngôn ngữ tính toán vị từ ếbi n bộ 33 Tính an toàn của các biểu thức { Đặt vấn đề: {t| ¬(t∈r )} { K/n miền giá trị của biểu thức: DOM(P) z Các hằng xuất hiện trong P z Các giá trị của các thuộc tính của các bộ của các quan hệ xuất hiện trong P 34 ™ Ví dụ: P(t) = t∈Sport ^ t[Sport] != ‘‘Football’’ DOM(P)={‘‘05’’, ‘‘09’’, ‘‘Swimming’’, ‘‘Dancing’’, ‘‘Football’’} SportID Sport 05 Swimming 09 Dancing Sport Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 18 Biểu thức an toàn { Đ/n : {t | P(t)} là an toàn nếu tất cả các giá trị ấ ếxu t hiện trong k t quả là các giá trị từ DOM(P) ™ Ví dụ 9 P(t) = t∈Sport ^ t[Sport] != ‘‘Football’’ (an toàn) 8 P(t) = ¬(t∈Sport) (không an toàn) 35 Phép tính vị từ biến miền { Đ/n: là ngôn ngữ vị từ có biến là các miền giá trị { Định nghĩa hình thức { | P(x1, , xn)} z x1, , xn là các biến miền hay các hằng miền z P: là một biểu thức chỉ có các biến tự do xi 36 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 19 Các biểu thức nguyên tố { ∈ r z r là 1 quan hệ trên n thuộc tính z x1, , xn là các biến miền hay các hằng miền. { x θ y z x và y là các biến miền z θ là một phép so sánh đơn giản ( , ≤ , ≠ , ≥). ™ Ví dụ 37 ∈ Student ∈ Enrol x = u Các biểu thức nguyên tố (2) { x θ c z x là một biến miền z θ là một phép so sánh z c là một hằng trong miền của thuộc tính củax ™ Ví dụ Z = ‘‘Bundoora’’ 38 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 20 Biểu thức tổng quát { Một biểu thức nguyên tố là một biểu thức. { P1 là 1 ⇒ ¬P1, (P1) là biểu thức { P1 và P2 là biểu thức ⇒ P1 ∧ P2 , P1 ∨ P2 , P1 ⇒ P2 là biểu thức 39 { P1(x) là một biểu thức ⇒ ∃ x (P1(x)), ∀ x (P1(x)) là biểu thức Tính an toàn của các biểu thức { Đ/n: một biểu thức { | P(x1, , xn)} là ế ấ ấan toàn n u t t cả các giá trị xu t hiện trong kết quả là các giá trị từ DOM(P) 40 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 21 Bài tập { Biểu diễn câu hỏi bằng ngôn ngữ tính toán vị từ ế ềbi n mi n 41 Nhận xét { Sự tương đương của 3 ngôn ngữ z Đại số quan hệ z Phép tính vị từ biến bộ hạn chế với các biểu thức an toàn z Phép tính vị từ biến miền hạn chế với các biểu thức an toàn { So sánh đặc điểm của 3 ngôn ngữ 42 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 22 Khả năng bổ sung của các ngôn ngữ { Tính toán số học: các phép toán số học +,-,*,/ { Lệnh gán và hiển thị: hiển thị quan hệ kết quả hay gán một quan hệ đã được tính toán đến một tên quan hệ khác. Hà tậ h tí h iá t ị t bì h tí h tổ 43 { m p ợp: n g r rung n , n ng, chọn giá trị nhỏ nhất hay lớn nhất Bài tập biến đổi tương đương { Viết định nghĩa các phép toán ĐSQH với các ểbi u thức tính toán vị từ { Tìm biểu thức tương đương trong ngôn ngữ ĐSQH và ngôn ngữ tính toán vị từ 44 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 23 45 Ngôn ngữ QBE Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 24 QBE (Query-By-Example) { Là một ngôn ngữ truy vấn dữ liệu { Các câu truy vấn được thiết lập bởi một giao diện đồ hoạ { Phù hợp với các câu truy vấn đơn giản, tham hiế đế ít bả 47 c u n ng { Một số sản phẩm: IBM (IBM Query Management Facility), Paradox, MS. Access, ... Truy vấn trên một quan hệ Student ID Name Suburb { P.~ Print { phép tính vị từ biến miền tương đương: {|∃i,x,s (i,x,s) ∈ Student ^ s = ‘‘Bundoora’’} P._x Bundoora 48 { Biểu thức đại số quan hệ tương đương )(" StudentBundoorasuburb=σ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 25 { Lựa chọn tất cả các cột Truy vấn trên một quan hệ (tiếp) { Sắp xếp Student ID Name Suburb P. Bundoora 49 Student ID Name Suburb P.AO(1) P.AO(2) • AO: sắp xếp tăng dần • DO: sắp xếp giảm dần Các truy vấn trên nhiều quan hệ { Đưa ra tên của các sinh viên có đăng ký ít nhất ột kh á hm o ọc { Đưa ra tên các sinh viên không đăng ký một khoá học nào Student ID Name Suburb _id P._name Enrol SID Course _id 50 Student ID Name Suburb _id P._name Enrol SID Course ¬ _id Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 26 Các tính toán tập hợp { Các phép toán: AVG, COUNT, MAX, MIN, SUM { Ví dụ: đưa ra tên các thành phố và số lượng sinh viên đến từ thành phố đó Student ID Name Suburb 51 { G. ~ Grouping _id G.P. P.COUNT._id Hộp điều kiện { Được sử dụng để biểu diễn z Điều kiện trên nhiều hơn 1 thuộc tính z Điều kiện trên các trường tính toán tập hợp { Ví dụ: đưa ra danh sách các thành phố có nhiều hơn 5 sinh viên ¬ x Student ID Name Suburb Condition 52 _id G.P. COUNT._id > 5 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 27 Các thao tác thay đổi dữ liệu { Xoá { Thêm Student ID Name Suburb D. 1108 Student ID Name Suburb I. 1179 David Evry 53 { Sửa đổi Student ID Name Suburb 1179 U.Paris Tính đầy đủ của QBE { Có thể biểu diễn cả 5 phép toán đại số cơ sở (σ,Π,∪,\,x) { Bài tập: chứng minh tính đầy đủ của QBE 54 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 28 Định nghĩa dữ liệu trong QBE { sử dụng cùng qui cách và giao diện đồ hoạ như đối ới t ấ v ruy v n. I.Student I. ID Name Suburb KEY I. Y N N TYPE I. CHAR(5) CHAR(30) CHAR(30) 55 DOMAIN I. Sid SName Surb INVERSION I. Y N N Định nghĩa dữ liệu trong QBE (2) { Các khung nhìn Student ID Name Suburb Enrol SID Course I.View V I. ID Name Course I. _id _name _course 56 _id _name _id _course Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 29 Ngôn ngữ SQL SQL (Structured Query Language) { 1975: SEQUEL z System-R { 1976: SEQUEL2 { 1978/79: SQL z System-R { 1986: chuẩn SQL-86 1989 h ẩ SQL 89 58 { : c u n - { 1992: chuẩn SQL-92 { 1996: chuẩn SQL-96 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 30 Các thành phần của SQL { Ngôn ngữ mô tả dữ liệu (Data Definition Language) ấz C u trúc các bảng CSDL z Các mối liên hệ của dữ liệu z Quy tắc, ràng buộc áp đặt lên dữ liệu { Ngôn ngữ thao tác dữ liệu (Data Manipulation Language) z Thêm, xoá, sửa dữ liệu trong CSDL Ngôn ngữ quản lý dữ liệu (D t C t l L ) 59 { a a on ro anguage z Thay đổi cấu trúc của các bảng dữ liệu z Khai báo bảo mật thông tin z Quyền hạn của người dùng trong khai thác CSDL Cú pháp câu lệnh truy vấn SQL SELECT [DISTINCT] , , FROM ,, [WHERE ] [GROUP BY , , ] [ORDER BY | [ASC|DESC]] 60 [HAVING ] Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 31 Truy vấn đơn giản trên 1 bảng {Tìm thông tin từ các cột của bảng ¾ SELECT ColumnName, ColumnName, ... FROM TableName; ¾ SELECT * FROM TableName; {Ví dụ SELECT Name 61 FROM Student; Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student Name Robert Glen Norman Mary ∏ )(Studentname Truy vấn với điều kiện lựa chọn {Chọn các bản ghi (dòng) SELECT C l N C l N o umn ame, o umn ame, ... FROM TableName WHERE condition_expression; {Ví dụ SELECT * FROM Student WHERE suburb=‘‘Bundoora’’ ; 62 Id Name Suburb 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn Student Id Name Suburb 3936 Glen Bundoora 8507 Norman Bundoora )(" StudentBundoorasuburb=σ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 32 Biểu diễn điều kiện lựa chọn {Các phép toán quan hệ: =, !=, , = {Các phép toán logic: NOT, AND, OR {Phép toán phạm vi: BETWEEN, IN, LIKE z Kiểu dữ liệu số 63 { attr BETWEEN val1 AND val2 (⇔ (attr>=val1) and (attr<=val2) ) { attr IN (val1, val2, ...) (⇔ (attr=val1) or (attr=val2) or ... ) z Kiểu dữ liệu xâu { LIKE: sử dụng đối sánh mẫu xâu với các ký tự % (thay thế cho 1 ký tự bất kỳ), * (thay thế cho 1 xâu ký tự bất kỳ) Bài tập { Viết câu lệnh SQL đưa ra danh sách tên sinh iê h ô ‘‘D t b ’’ h ặ ô ‘‘VB’’v n ọc m n a a ase o c m n { Viết câu lệnh SQL đưa ra danh sách các sinh viên đăng ký các khoá học có mã 113 hoặc 101 64 { Đưa ra danh sách các khoá học (Course) mà tên của khoá học chứa cụm ‘‘CS’’ Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 33 Ví dụ Id Name Suburb Student Takes SID SNO 1108 21 SID Course Enrol 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn 1108 23 1108 29 8507 23 8507 29 3936 101 1108 113 8507 101 No Name Dept 21 Systems CSCE Subject No Name Dept Course 65 23 Database CSCE 29 VB CSCE 18 Algebra Maths 113 BCS CSCE 101 MCS CSCE Truy vấn phức tạp trên nhiều bảng { Điều kiện kết nối SELECT T1.C1,T1.C2,T2.C1,T2.C4, ... FROM T1, T2 WHERE condition_expression { Ví dụ: đưa ra danh sách mã sinh vien (Id), tên sinh viên (Name), thành phố (Suburb), mã khoá học (Course) mà các sinh viên đã đăng ký 66 SELECT Id, Name, Suburb,Course FROM Student,Enrol WHERE Id=SID Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 34 Phép toán đổi tên { Từ khoá AS SELECT c1 as name1, c2 as name2 FROM TableName { Ví dụ: đưa ra danh sách mã sinh viên, tên sinh viên và tên môn học mà sinh viên đó tham gia SELECT SID , Student.Name as SName, Subject.Name as Subject 67 FROM Student,Takes, Subject WHERE (Id=SID) and (SNO = No) Sử dụng biến bộ trong SQL { Sử dụng từ khoá AS trong mệnh đề FROM { Ví dụ SELECT SID , Stud.Name as SName, Sub.Name as Subject FROM Student as Stud,Takes, Subject as Sub WHERE (Id=SID) and (SNO = No) 68 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 35 Loại trừ các bản ghi trùng nhau { Từ khoá DISTINCT SELECT DISTINCT , , FROM ,, { Ví dụ: đưa ra danh sách tên các khoa (dept) tương ứng với các khoá học (Course). Mỗi giá trị chỉ hiện thị một lần SELECT DISTINCT Dept 69 FROM Course Tìm kiếm có sắp xếp { Sắp xếp các bản ghi kết quả theo một thứ tự h t ớc o rư c SELECT , , FROM ,, [WHERE ] ORDER BY | [ASC|DESC] { Ví dụ: đưa ra danh sách tên các sinh viên theo thứ tự tăng dần 70 SELECT Name FROM Student ORDER BY Name ASC Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 36 Phân nhóm các bản ghi kết quả { Phân nhóm các bản ghi kết quả theo giá trị của 1 hoặc nhiều thuộc tính SELECT , , FROM ,, [WHERE ] [GROUP BY , , ] { Ví dụ đưa ra tên các sinh viên nhóm theo thành phố của sinh viên đó SELECT Suburb Name 71 , FROM Student GROUP BY Suburb SELECT Suburb, Count(Id) FROM Student GROUP BY Suburb Điều kiện hiển thị các bản ghi kết quả { Lựa chọn các bản ghi kết quả để hiển thị SELECT , , FROM ,, [WHERE ] HAVING { Ví dụ: đưa ra tên các thành phố có nhiều hơn 3 sinh viên SELECT Suburb COUNT(ID) 72 , FROM Student GROUP BY Suburb HAVING COUNT(ID) > 3 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 37 Các phép toán tập hợp { UNION, MINUS, INTERSECT { Ví dụ: đưa ra danh sách tên các môn học không có sinh viên nào tham dự SELECT DISTINCT Subject.Name FROM Subject MINUS SELECT DISTINCT Subject.Name 73 FROM Student, Takes, Subject WHERE Student.Id = Takes.SID and Takes.SNO = Subject.No Các câu truy vấn lồng nhau { Là trường hợp các câu truy vấn (con) được viết ồl ng nhau { Thường được sử dụng với để z Kiểm tra thành viên tập hợp (IN) z So sánh tập hợp (>ALL, >=ALL, <ALL,<=ALL,=ALL, NOT IN,SOME, ) z Kiểm tra các bảng rỗng (EXISTS hoặc NOT EXISTS) 74 { Các truy vấn con lồng nhau thông qua mệnh đề WHERE Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 38 Các hàm thư viện { Hàm tính toán trên nhóm các bản ghi z MAX/MIN z SUM z AVR z COUNT 75 Các hàm thư viện (2) { Hàm tính toán trên bản ghi z Hàm toán học: ABS, SQRT, LOG, EXP, SIGN, ROUND z Hàm xử lý xâu ký tự: LEN, LEFT, RIGHT, MID z Hàm xử lý thời gian: DATE, DAY, MONTH, YEAR, HOUR, MINUTE, SECOND z Hàm chuyển đổi kiểu giá trị: FORMAT 76 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 39 Bài tập { Viết các câu lệnh SQL biểu diễn các câu hỏi ấtruy v n { Viết biểu thức đại số quan hệ tương đương với các câu lệnh SQL 77 Các câu lệnh cập nhật dữ liệu { Thêm ¾ INSERT INTO table[(col1,col2,)] VALUES (exp1,exp2,) ¾ INSERT INTO table[(col1,col2,)] SELECT col1,col2, FROM tab1, tab2, WHERE 78 { Ví dụ ¾ INSERT INTO Student[Id, Name, Suburb] VALUES (‘‘1179’’,‘‘David’’,‘‘Evr’’) Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 40 Các câu lệnh cập nhật dữ liệu (2) { Xoá DELETE FROM table WHERE cond_exp; { Sửa UPDATE table SET col1 = exp1, col2=exp2, col2=exp2, WHERE cond_exp; Ví d 79 { ụ z DELETE FROM Student WHERE Suburb = ‘‘Bundoora’’; z UPDATE Student SET Suburb = ‘‘Evry’’ WHERE Suburb = ‘‘Evr’’; Định nghĩa dữ liệu với SQL { Các thông tin được định nghĩa bao gồm z Sơ đồ quan hệ z Kiểu dữ liệu hay miền giá trị của mỗi thuộc tính z Các ràng buộc toàn vẹn z Các chỉ số đối với mỗi bảng z Thông tin an toàn và uỷ quyền đối với mỗi bảng z Cấu trúc lưu trữ vật lý của mỗi bảng trên đĩa 80 ¾ Được biểu diễn bởi các lệnh định nghĩa dữ liệu Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 41 Cú pháp { Tạo bảng CREATE TABLE tab( col1 type1(size1)[NOT NULL], ..., col2 type2(size2)[NOT NULL], ..., ..... [CONSTRAINT clause] ... ) 81 { Xoá bảng DROP TABLE tab Quy ước đặt tên và kiểu dữ liệu { Quy ước đặt tên ố ấz 32 ký tự: chữ cái, s , d u _ { Kiểu dữ liệu (SQL-92) z CHAR(n) z VARCHAR(n) z Int z Smallint z Numeric(p d) 82 , z Real, double z float(n) z Date z time Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 42 Kiểu ràng buộc { RBTV về giá trị miền CONSTRAINT CHECK { RBTV về khoá chính CONSTRAINT PRIMARY KEY (fk1,fk2,) 83 { RBTV về khoá ngoại hay phụ thuộc tồn tại CONSTRAINT FOREIGN KEY (fk1,fk2,) REFERENCES tab(k1,k2) Thêm/xoá/sửa cột của các bảng { Thêm ALTER TABLE ADD COLUMN [NOT NULL] { Xoá ALTER TABLE DROP COLUMN { Sửa 84 ALTER TABLE CHANGE COLUMN Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 43 Thêm/sửa các ràng buộc { Thêm ALTER TABLE ADD CONSTRAINT { Sửa ALTER TABLE DROP CONSTRAINT 85 Ví dụ CREATE TABLE Student( Id char(10)[NOT NULL] Id Name Suburb 1108 Robert Kew, Name char(30)[NOT NULL], Suburb char(30), CONSTRAINT key_Stud PRIMARY KEY Id ) CREATE TABLE Takes( SID char(10)[NOT NULL] 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn 86 , SNO char(5)[NOT NULL], CONSTRAINT key_takes PRIMARY KEY (SID,SNO), CONSTRAINT key_2Stud FOREIGN KEY (SID) REFERENCES Student(Id) ) SID SNO 1108 21 1108 23 8507 23 8507 29 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 44 Xử lý câu hỏi truy vấn Câu lệnh SQL Phân tích cú pháp (parser) Biểu thức ĐSQH Bộ tối ưu (optimizer) Biểu thức ĐSQH tối ưu Bộ sinh mã 87 (code generator) Chương trình tối ưu Các điểm cần lưu ý { Các ngôn ngữ dữ liệu z ĐSQH vs. vị từ z QBE vs. SQL { Sự tương đương của các ngôn ngữ z Ngôn ngữ ĐSQH và ngôn ngữ vị từ z Biến đổi giữa câu truy vấn SQL và biểu thức đại số quan hệ 88 Nhập môn cơ sở dữ liệu Vũ Tuyết Trinh, b/m Các hệ thống thông tin, khoa CNTT, ĐHBKHN 45 89 Ví dụ Id Name Suburb Student Takes SID SNO SID Course Enrol 1108 Robert Kew 3936 Glen Bundoora 8507 Norman Bundoora 8452 Mary Balwyn 1108 21 1108 23 8507 23 8507 29 3936 101 1108 113 8507 101 No Name Dept 21 Systems CSCE Subject No Name Dept Course 90 23 Database CSCE 29 VB CSCE 18 Algebra Maths 113 BCS CSCE 101 MCS CSCE