Truy vấn, Ràng buộc, Trigger

Ngôn ngữ SQL có một số khía cạnh:

• Ngôn ngữ thực hiện dữ liệu - The Data Manipulation Language (DML): Là tập hợp các lệnh của SQL cho phép người dùng đưa ra các truy vấn, thêm, sửa và xóa các dòng dữ liệu trong bảng. Những truy vấn sẽ được tập trung trình bày trong chương này. Chúng ta đã khám phá DML để thêm, sửa, xóa dữ liệu trong Chương 3.
• Ngôn ngữ định nghĩa dữ liệu - The Data Defination Language (DDL): Là tập hợp các câu lệnh của SQL hỗ trợ để tạo, xóa và sửa định nghĩa các bảng và các khung nhìn. Các ràng buộc tham chiếu có thể định nghĩa trên bảng, hoặc có thể định nghĩa sau khi bảng được tạo ra. Chúng ta đã khám phá các đặc trưng DDL trong chương 3. Mặc dù chuẩn SQL không đề cập tới các chỉ số, nhưng các thực hiện của DBMS thương mại cũng cung cấp lệnh để tạo và xóa các chỉ số.
• Trigger và Các ràng buộc toàn vẹn nâng cao (Triggers and Advanced Integrity Constraints): Chuẩn SQL:1999 mới có những hỗ trợ cho thực hiện triggers, đó là tập những thao tác được DBMS thực hiện khi có bất kỳ sự thay đổi nào trong cơ sở dữ liệu. Chúng ta sẽ khám phá trigger và các ràng buộc trong chương này.
• SQL nhúng và động (Embedded và dynamic SQL):Các đặc trưng của Embedded SQL cho phép mã lệnh SQL được gọi từ một ngôn ngữ lập trình như C và COBOL. Các đặc trưng của Dynamic SQL cho phép một truy vấn được xây dựng và thực hiện run-time.
• Truy vấn cơ sở dữ liệu từ xa và các thực hiện Client-Server (Client-Server Execution and Remote Database Access): Những lệnh này điều khiển cách thức các chương trình ứng dụng trên Client kết nối với cơ sở dữ liệu trên Server, hoặc truy cập dữ liệu trong cơ sở dữ liệu trên mạng. Chúng ta sẽ khám phá những lệnh này trong Chương 7.
• Quản lý giao dịch (Transaction Management): Một loạt các lệnh cho phép người dùng điều khiển các giao dịch.
• Bảo mật (Security): SQL cung cấp cơ chế để điều khiển các truy cập của người dùng tới các đối tượng dữ liệu như các bảng và các khung nhìn.
• Các đặc trưng nâng cao (Advanced features): Chuẩn SQL:1999 bao gồm các tính năng hướng đối tượng (Chương 23), các truy vấn lồng nhau (Chương 24), truy vấn hỗ trợ quyết định (Chương 25), khai phá dữ liệu (Chương 26), dữ liệu không gian (Chương 28) và quản lý dữ liệu XML và văn bản (Chương 27).

• Tổ chức chương

Các phần của chương được tổ chức như sau. Phần 2 trình bày về các truy vấn SQL cơ bản và Phần 3 giới thiệu các phép toán tập hợp. Phần 4 trình bày về các truy vấn lồng nhau. Phần 5.5 khám phá các phép toán nhóm. Chúng ta bàn tới các giá trị null trong Phần 6. Phần 7 xem xét các lệnh SQL được nhúng trong ngôn ngữ lập trình và trong Phần 8 bàn tới các bộ giá trị trong quan hệ được truy cập như thế nào bằng con trỏ. Phần 9 trình bày như thế nào truy vấn được xây dựng sử dụng dynamic SQL, và trong Phần 10 bàn về hai chuẩn kết nối tới DBMS là ODBC và JDBC. Phần 11 bàn về các ràng buộc toàn vẹn phức tạp được thực hiện trong SQL DDL, phần tiếp theo của những phần đã trình bày trong Chương 3; những ràng buộc mới cho phép chúng ta tận dụng đầy đủ khả năng của ngôn ngữ truy vấn SQL.

Cuối cùng, Phần 12 và 13 trình bày về khái niệm cơ sở dữ liệu hoạt động. Một cơ sở dữ liệu hoạt động có một tập các triggers do các DBA định nghĩa. DBMS điều khiển các cơ sở dữ liệu, phát hiện tình trạng hiện tại của cơ sở dữ liệu và gọi đến các trigger.

Các ví dụ:

Chúng ta sẽ xem xét một số ví dụ truy vấn sử dụng các bảng được định nghĩa sau:

Sailors(sid: integer, sname: string, rating: integer, age: real)

Boats(bid: integer, bname: string, color: string)

Reserves(sid: integer, bid: integer, day: date)

Chúng ta sẽ cung cấp cho mỗi truy vấn một mã duy nhất, và sử dụng tiếp các truy vấn trong Chương 4. Truy vấn mới đầu tiên của chương này có mã số Q15. Truy vấn từ Q1 đến Q14 được định nghĩa trong Chương 4. Chúng ta sử dụng minh họa quan hệ S3 của Sailors, R2 của Reserves, và B1 của Boats giới thiệu trong Chương 4, hình 1, 2 và 3 để làm dữ liệu ví dụ cho các truy vấn.

Tất cả các bảng và truy vấn trong chương này có thể được tìm thấy trong Website:

http://www.cs.wisc.edu/~dbbook

Những vấn đề được chỉ ra bao gồm cách thức thực hiện câu lệnh SQL trong Oracle, IBM DB2, Microsoft SQL Server, và MySQL, và các scripts để tạo các bảng và các truy vấn ví dụ.

Phần này trình bày cú pháp của truy vấn SQL đơn giản và giải thích ý nghĩa của nó thông qua chiếnlược thực hiện truy vấn ở mức khái niệm. Chiến lược thực hiện truy vấn ở mức khái niệm là cách để thực hiện truy vấn theo chiều hướng dễ hiểu, không tập trung vào hiệu năng. DBMS sẽ thực hiện truy vấn có thể theo một cách khác hiệu quả hơn cách người dùng định nghĩa.

Dạng đơn giản của truy vấn SQL như sau:

Tất cả các truy vấn phải có mệnh đề SELECT, chỉ ra những cột nào được đưa ra trong kết quả, và mệnh đề FROM xác định bảng chứa những cột liên quan trong câu lệnh. Mệnh đề WHERE xác định điều kiện chọn.

Chúng ta đã biết rằng một truy vấn liên quan tới các biểu thức trong đại số quan hệ, bao gồm phép chọn, phép chiếu, phép nhân chéo. Quan hệ chặt chẽ giữa đại số quan hệ và SQL là nền tảng để tối ưu hóa truy vấn trong DBMS quan hệ, chúng ta sẽ xem trong Chương 12 và 15. Kế hoạch thực hiện truy vấn SQL được biểu diễn sử dụng hàng loạt các biểu thức đại số quan hệ (Phần 15.1).

Minh họa S3 của Sailors

Minh họa R2 của Reserves

(Q15) Đưa ra tên và tuổi của tất cả các thủy thủ.

SELECT DISTINCT S.sname, S.age
FROM Sailors S

Minh họa B1 của Boats

Hình 3 Minh họa B1 của Boats

Kết quả của truy vấn này trong trường hợp có và không có từ khóa DISTINCT dựa trên minh họa của Sailors chỉ ra trong Hình 4 và 5. Chỉ có sự khác nhau trong hai kết quả này là sự xuất hiện hay không xuất hiện của bộ giá trị có tên là Horatio và tuổi là 35.

Truy vấn tiếp theo có sử dụng đến phép chọn trong đại số quan hệ.

Kết quả của Q15 Kết quả của Q15 bỏ DISTINCT

SELECT S.sid, S.sname, S.rating, S.age
FROM Sailors AS S
WHERE S.rating > 7

Như hai ví dụ minh họa ở trên, mệnh đề SELECT được sử dụng để thực hiện phép chiếu, trong khi phép chọn trong đại số quan hệ được biểu diễn sau mệnh đề WHERE! Chúng ta sẽ đề cập chi tiết đến các cú pháp của truy vấn SQL đơn giản:

• Cụm from-list sau mệnh đề FROM là danh sách tên các bảng. Tên bảng có thể được thay bằng một biến miền, biến miền hữu ích khi cùng một tên bảng xuất hiện nhiều hơn một lần trong from-list.
• Cụm select-list là danh sách tên các cột của bảng trong from-list. Các tên cột có thể có tiền tố là các biến miền.
• Qualification trong mệnh đề WHERE là một tổ hợp logic (tức là, biểu thức sử dụng kết nối logic AND, OR, NOT) của các điều kiện theo dạng Biểu_thứcopBiểu_thức, trong đó op là một trong những toán tử logic >, <, >=, <=, =. Một biểu thức là tên cột, hằng số, hoặc một biểu thức (toán học hoặc xâu ký tự).
• Từ khóa DISTINCT là từ khóa lựa chọn. Nó chỉ ra rằng bảng kết quả của truy vấn có nên chứa bộ giá trị trùng nhau không. Mặc định, những bộ giá trị trùng nhau không được phép loại bỏ.

Mặc dù, những quy tắc phía trên biểu diễn cú pháp của truy vấn SQL cơ bản, nhưng chúng không nói cho chúng ta biết ý nghĩa của một truy vấn. Kết quả của một truy vấn là một quan hệ bao gồm một tập các dòng! - những nội dung này có thể được hiểu bằng việc xem xét chiến lược thực hiện truy vấn ở mức khái niệm sau:

1. Tìm kết quả của phép nhân chéo trên các bảng trong from-list.
2. Xoá các dòng trong kết quả của phép nhân chéo không thoả mãn các điều kiện trong qualification.
3. Xoá tất cả các cột không xuất hiện trong select-list.
4. Nếu có từ khoá DISTINCT, kết quả của truy vấn sẽ bỏ đi những dòng trùng nhau.

Chiến lược thực hiện truy vấn ở mức khái niệm nói trên làm chúng ta nhìn thấy rõ ràng những dòng nào cần được xuất hiện trong kết quả. Tuy nhiên, nó dường như là không thực sự hiệu quả (tốc độ thực hiện truy vấn chậm). Chúng ta sẽ xem xét chi tiết về cách thực hiện một truy vấn như thế nào trong Chương 12 và 13; bây giờ, mục đích của chúng ta chỉ đơn giản là giải thích ý nghĩa của truy vấn. Chúng ta sử dụng truy vấn sau để minh hoạ chiến lược thực hiện truy vấn ở mức khái niệm:

(Q1) Tìm tên các thuỷ thủ đã phục vụ trên tàu mã số 103.

Câu lệnh SQL như sau:

SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid=103

Bước đầu tiên là xây dựng phép nhân chéo S4 × R3, kết quả chỉ ra trong Hình 8.

Bước thứ hai là áp dụng điều kiện S.sid=R.sid AND R.bid=103 (Ghi nhớ rằng phép so sánh đầu tiên là yêu cầu của phép nối). Bước này loại bỏ tất cả trừ dòng cuối cùng trong minh hoạ chỉ ra trong Hình 5.8. Bước thứ 3 là loại bỏ những cột không cần thiết, chỉ sname xuất hiện trong mệnh đề SELECT là cần được giữ lại. Bước này đưa chúng ta đến kết quả trong Hình 9, bảng chỉ có một cột và một dòng.

Minh hoạ R3 của Reserves

Hình 6. Minh hoạ R3 của Reserves

Minh hoạ S4 của Sailors S4 × R3 Kết quả của truy vấn Q1 trên R3 v S4

Bây giờ, chúng tôi trình bày một số ví dụ truy vấn, rất nhiều trong số này đã được trình bày trong phần đại số quan hệ và các phép toán logic (Chương 4). Ví dụ đầu tiên minh hoạ cách sử dụng các biến miền là biến không bắt buộc. Truy vấn Q1 bàn đến trong phần trước có thể được viết như sau:

SELECT sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND bid=103

SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid = Reserves.sid AND bid=103

(Q16) Tìm các sid của thuỷ thủ đã phục vụ trên tàu màu 'red'.

SELECT R.sid
FROM Boats B, Reserves R
WHERE B.bid = R.bid AND B.color = 'red'

(Q2) Tìm tên các thuỷ thủ đã phục vụ trên tàu màu 'red'.

FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'

Truy vấn này chứa phép nối giữa ba bảng, tiếp đến là điều kiện chọn màu của tàu. Kết nối với Sailors cho phép chúng ta tìm tên các thuỷ thủ.

(Q3) Tìm màu của các tàu đã được Lubber phục vụ.

SELECT B.color
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND S.sname = 'Lubber'

(Q4) Tìm tên các thuỷ thủ đã phục vụ trên ít nhất một tàu.

SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid

SQL hỗ trợ một phiên bản chung của select-list, trong đó không chỉ có danh sách các cột. Mỗi mục trong select-list có thể có dạng biểu-thức AS tên-cột, trong đó biểu-thức là một biểu thức toán hoặc một xâu ký tự của các cột (tiền tố có thể là một biến miền) và các hằng số. Nó có thể chứa hàm nhóm như sum và count, phần này chúng ta sẽ bàn trong Phần 5. Chuẩn SQL cũng hỗ trợ các biểu thức dạng ngày và tháng, phần này chúng ta sẽ không bàn ở đây. Mặc dù không phải là một thành phần của chuẩn SQL, rất nhiều các hệ thống thực thi cũng hỗ trợ sử dụng các hàm hỗ trợ như sqrt, sin và mod.

Biểu thức thông thường trong SQL: Phản ảnh tầm quan trọng của dữ liệu dạng ký tự, SQL: 1999 bao gồm một toán tử mạnh LIKE, còn gọi là SIMILAR. Toán tử này hỗ trợ mạnh mẽ việc tìm kiếm ký tự. Những biểu thức thông thường cũng được hệ điều hành Unix hỗ trợ phục vụ cho tìm kiếm xâu ký tự, mặc dù cú pháp có khác nhau một chút.

Đại số quan hệ và SQL: Tập các phép toán trong SQL có thể thực thi trong đại số quan hệ.

(Q17) Tăng rating lên 1 cho những người đã phục vụ trên hai tàu khác nhau trong một ngày.

SELECT S.sname, S.rating+1 AS rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND S.sid = R2.sid 
AND R1.day = R2.day AND R1.bid <> R2.bid

SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S2
WHERE 2*S1.rating = S2.rating-1

Thêm vào đó, SQL cung cấp khả năng so sánh mẫu thông qua toán tử LIKE, cùng với việc sử dụng ký hiệu % (thay thế cho số không hoặc nhiều ký tự bất kỳ) và _ (thay thế cho chính xác một ký tự bất kỳ). Vì thế '_AB%' là một mẫu phù hợp với tất cả các xâu có chứa ít nhất 3 ký tự, trong đó ký tự thứ 2 và thứ 3 là A và B tương ứng. Ghi nhớ rằng, không như những toán tử so sánh khác, ký tự trống cũng quan trọng trong toán tử LIKE. Vì thế, 'Jept' ='Jept ' là đúng, trong khi 'Jept' LIKE 'Jept' là sai. Một ví dụ sử dụng LIKE trong truy vấn như sau:

(Q18) Tìm tuổi các thuỷ thủ có tên bắt đầu và kết thúc bằng ký tự B và có ít nhất 3 ký tự.

SELECT S.age
FROM Sailors S
WHERE S.sname LIKE 'B_%B'

Hợp, giao và trừ

SQL cung cấp 3 cấu trúc thực hiện trên tập hợp, phần mở rộng của các dạng truy vấn cơ bản phía trước. Vì kết quả của một truy vấn là tập hợp các dòng nên việc xem xét đến các phép toán như hợp (UNION), giao (INTERSECT), trừ (EXCEPT) là rất tự nhiên. SQL hỗ trợ ba phép toán này thông qua từ khoá UNION, INTERSECT, and EXCEPT. SQL cũng cung cấp các phép toán tập hợp