Ngôn ngữ SQL
Ngôn ngữ SQL(structure 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ấ 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.
NGÔN NGỮ SQL
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
MỤC LỤC Trang
Lời mở đầu 2
Mục lục 3
Chương I SQL cơ bản 6
1.1 Các truy vấn đơn giản trong SQL 6
1.1.1 Phép chiếu trong SQL 9
1.1.2 Phép chọn trong SQL 10
1.1.3 So sánh các xâu 11
1.1.4 Ngày tháng và thời gian 12
1.1.5 Các giá trị null và các so sánh bao hàm null 13
1.1.6 Giá trị logic UNKNOWN 14
1.1.7 Sắp xếp thứ tự dữ liệu ra 15
1.2 Các truy vấn bao gồm nhiều hơn một quan hệ 17
1.2.1 Tích và nối trong SQL 17
1.2.2 Làm rõ nghĩa các thuộc tính 19
1.2.3 Các biến bộ 20
1.2.4 Các phép hợp, giao, trừ các truy vấn 21
1.3 Các truy vấn con 23
1.3.1 Các truy vấn con tạo ra các giá trị vô hướng 23
1.3.2 Các điều kiện có bao hàm các quan hệ 23
1.3.3 Các điều kiện có bao hàm các bộ 25
1.3.4 Các truy vấn con tương quan với nhau 26
1.3.5 Các truy vấn con trong mệnh đề FROM 27
1.3.6 Các biểu thức nối của SQL 28
1.3.7 Nối tự nhiên 28
1.3.8 Nối ngoài 29
1.4 Các phép toán quan hệ đầy đủ 29
1.4.1 Loại bỏ trùng lặp 32
1.4.2 Trùng lặp trong phép hợp, phép giao, phép trừ 26
1.4.3 Nhóm và sự kết hợp trong SQL 27
1.4.4 Các phép toán nhóm 28
1.4.5 Nhóm 29
1.4.6 Các mệnh đề HAVING 31
1.5 Sửa đổi cơ sở dữ liệu 36
1.5.1 Chèn 32
1.5.2 Xóa 34
1.5.3 Cập nhật 35
1.6 Định nghĩa một lược đồ quan hệ trong SQL 39
1.6.1 Các kiểu dữ liệu 36
1.6.2 Các khai báo bảng đơn giản 37
1.6.3 Sửa đổi các lược đồ quan hệ 37
1.6.4 Các giá trị ngầm định 38
1.6.5 Các chỉ số 38
3
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
1.6.6 Nhập môn về lựa chọn chỉ số 34
1.7 Khung nhìn 44
1.7.1 Khai báo các khung nhìn 44
1.7.2 Truy vấn các khung nhìn 45
1.7.3 Đặt tên lại các thuộc tính 46
1.7.4 Sửa đối các khung nhìn 47
1.7.5 Giải thích các truy vấn có chứa các khung nhìn 48
1.8 Tổng kết chương I và bài tập 49
Chương II Các ràng buộc và các trigger 57
2.1 Khóa và khóa ngoài 58
2.1.1 Mô tả khóa chính 59
2.1.2 Các khóa được mô tả với UNIQUE 59
2.1.3 Làm có hiệu lực các ràng buộc khóa 54
2.1.4 Mô tả các ràng buộc khóa ngoài 55
2.1.5 Duy trì toàn vẹn tham chiếu 56
2.1.6 Làm chậm việc kiểm tra ràng buộc 58
2.2 Các ràng buộc trên các thuộc tính và các bộ 60
2.2.1 Các ràng buộc NOT NULL 61
2.2.2 Các ràng buộc kiểm tra dựa trên các thuộc tính 61
2.2.3 Các ràng buộc kiểm tra dựa trên các bộ 63
2.3 Sửa đổi các ràng buộc 64
2.3.1 Đặt tên cho các ràng buộc 64
2.3.2 Sửa đổi các ràng buộc trên các bảng 64
2.4 Các ràng buộc mức lược đồ và các trigger 65
2.4.1 Khẳng định ( Assertion) 65
2.4.2 Trigger 69
2.4.3 Các trigger trong SQL 69
2.4.4 Các trigger Instead-of 74
2.5 Tổng kết chương II và bài tập 75
Chương III Lập trình 77
3.1 SQL trong môi trường lập trình 77
3.1.1 Vấn đề trở ngại không phù hợp 78
3.1.2 Giao diện ngôn ngữ chủ/SQL 79
3.1.3 Phần khai báo 80
3.1.4 Sử dụng các biến dùng chung 81
3.1.5 Các câu lệnh Select đơn hàng 82
3.1.6 Con trỏ ( Cursor) 84
3.1.7 Cập nhật bằng con trỏ 87
3.1.8 Bảo vệ khỏi sự cập nhật đồng thời 88
3.1.9 Con trỏ cuộn 90
3.1.10 SQL động 91
3.2 Các thủ tục được lưu giữ trong lược đồ 93
4
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
3.2.1 Tạo các hàm và các thủ tục PSM 93
3.2.2 Một vài dạng câu lệnh đơn giản trong PSM 95
3.2.3 Các câu lệnh rẽ nhánh 96
3.2.4 Các truy vấn trong PSM 98
3.2.5 Vòng lặp trong PSM 99
3.2.6 Vòng lặp FOR 101
3.2.7 Những câu lệnh lặp khác 102
3.2.8 Những loại trừ trong PSM 103
3.2.9 Sử dụng các hàm và các thủ tục PSM 105
3.3 Môi trường SQL 105
3.3.1 Môi trường 106
3.3.2 Lược đồ 107
3.3.3 Danh mục 108
3.3.4 Client và Server trong môi trường SQL 109
3.3.5 Kết nối 109
3.3.6 Phiên 110
3.3.7 Module 111
3.4 Sử dụng giao diện mức gọi 112
3.4.1 Nhâp môn SQL/CLI 112
3.4.2 Xử lý các lệnh 115
3.4.3 Lấy dữ liệu ra từ kết quả truy vấn 116
3.5 Giao tác trong SQL 118
3.5.1 Xếp hàng theo thứ tự 119
3.5.2 Atomicity 121
3.5.3 Giao tác 123
3.5.4 Read-only transaction 124
3.5.5 Dirty read 125
3.5.6 Các mức cô lập khác 128
3.6 An toàn và cấp quyền trong SQL 137
3.6.1 Các quyền 138
3.6.2 Tạo các quyền 139
3.6.3 Tiến trình kiểm tra các quyền 140
3.6.4 Cấp các quyền 141
3.6.5 Biểu đồ grant 143
3.6.6 Hủy bỏ các quyền 144
3.7 Tổng kết chương III và bài tập 149
Phụ lục I : Cơ sở dữ liệu “CÔNGTY” 153
Phụ lục II: Cơ sở dữ liệu “MÁY TÍNH” 156
Tài liệu tham khảo 157
5
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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.
• 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.
6
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
• 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 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:
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
7
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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;
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;
8
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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
Ở 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
9
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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 =, 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 nNgôn ngữ SQL – Tác giả: Nguyễn Tuệ
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,..
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.
11
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
Để 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.
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 < trên ngày tháng
chứng tỏ ngày tháng thứ nhất sớm hơn ngày tháng thứ hai, còn dấu < trên thời gian
chứng tỏ thời gian thứ nhất sớm hơn thời gian thứ hai.
1.1.5 Các giá trị NULL và các so sánh bao hàm NULL.
SQL cho phép các thuộc tính có giá trị đặc biệt NULL, được gọi là giá trị null.
Một thuộc tính có giá trị null khi không biết giá trị của nó hoặc khi giá trị là không
áp dụng được hoặc giá trị bị giấu.
Trong mệnh đề WHERE, chúng ta có thể được chuẩn bị cho khả năng một
thành phần của một bộ nào đó là null. Có hai quy tắc quan trọng cần phải nhớ khi
chúng ta làm phép toán trên các giá trị null.
1.Khi chúng ta làm phép toán một giá trị null và một giá trị nào đó, bao gồm cả giá
trị null khác, bằng việc sử dụng một phép toán số học như là * hoặc + , kết quả
phép toán là null.
2.Khi chúng ta so sánh một giá trị null và một giá trị khác, bao hàm cả giá trị null
khác, bằng cách sử dụng các phép so sánh như là = hoặc >, 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ó giá trị
12
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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 it 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ó it 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
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ị
13
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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, …. Để 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 < danh sách các thuộc tính >
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
14
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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ụ:
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
15
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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
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.
16
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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.
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 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.
17
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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.
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à
18
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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 .
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à 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
19
Ngôn ngữ SQL – Tác giả: Nguyễn Tuệ
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
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 )
20
