Bài giảng Cơ sở dữ liệu - Chương 3: Ngôn ngữ định nghĩa và thao tác dữ liệu đối với mô hình quan hệ - Nguyễn Hồng Phương

ấn trên một quan hệ (tiếp)
• Lựa chọn tất cả các cột
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
• Sắp xếp
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 một khoá học
Student ID Name Suburb
_id P._name
Enrol SID Course
_id
50
Student ID Name Suburb
_id P._name
Enrol SID Course
 _id
• Đưa ra tên các sinh viên không đăng 
ký một khoá học nào
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ố đó
51
• G. ~ Grouping
Student ID Name Suburb
_id G.P. P.COUNT._id
Hộp điều kiện
• Được sử dụng để biểu diễn
–Điều kiện trên nhiều hơn 1 thuộc tính
–Đ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 
52
phố có nhiều hơn 5 sinh viên 
Student ID Name Suburb
_id G.P.
Condition
COUNT._id > 5
Các thao tác thay đổi dữ liệu
• Xóa
Student ID Name Suburb
D. 1108
• Thêm
53
Student ID Name Suburb
I. 1179 David Evry
Student ID Name Suburb
1179 U.Paris
• Sửa
Tính đầy đủ của QBE
• Có thể biểu diễn cả 5 phép toán đại 
số cơ sở (,,,\,x)
54
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
10
Định nghĩa dữ liệu trong QBE
• sử dụng cùng qui cách và giao diện 
đồ họa như đối với truy vấn.
55
I.Student I. ID Name Suburb
KEY I. Y N N
TYPE I. CHAR(5) CHAR(30) CHAR(30)
DOMAIN I. Sid SName Surb
INVERSION I. Y N N
• Các khung nhìn
Định nghĩa dữ liệu trong QBE 
(tiếp)
I.View V I. ID Name Course
I id name course
56
Student ID Name Suburb
_id _name
Enrol SID Course
_id _course
. _ _ _
Ngôn ngữ SQL
57
SQL (Structured Query Language)
• 1975: SEQUEL
– System-R
• 1976: SEQUEL2
• 1978/79: SQL
58
– System-R
• 1986: chuẩn SQL-86
• 1989: chuẩn SQL-89
• 1992: chuẩn SQL-92
• 1996: chuẩn SQL-96
Các thành phần của SQL
• Ngôn ngữ định nghĩa dữ liệu (Data Definition Language)
– Cấu trúc các bảng CSDL
– Các mối liên hệ của dữ liệu
– Quy tắc, ràng buộc áp đặt lên dữ liệu
N ô ữ th tá dữ liệ ( l )
59
• g n ng ao c u Data Manipu ation Language
– Thêm, xoá, sửa dữ liệu trong CSDL
– Truy vấn dữ liệu
• Ngôn ngữ điều khiển dữ liệu (Data Control Language)
– Khai báo bảo mật thông tin
– Quyền hạn của người dùng trong khai thác CSDL
Ngôn ngữ định nghĩa dữ liệu
• Các thông tin được định nghĩa bao gồm
– Sơ đồ quan hệ
– Kiểu dữ liệu hay miền giá trị của mỗi thuộc
tính
60
– Các ràng buộc toàn vẹn
– Các chỉ số đối với mỗi bảng
– Thông tin an toàn và ủy quyền đối với mỗi
bảng
– Cấu trúc lưu trữ vật lý của mỗi bảng trên đĩa
Được biểu diễn bởi các lệnh định nghĩa dữ
liệu
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
11
Quy ước đặt tên và kiểu dữ liệu
• Quy ước đặt tên
– 32 ký tự: chữ cái, số, dấu _
• Kiểu dữ liệu (SQL-92)
– CHAR(n)
– VARCHAR(n)
61
– Int
– Smallint
– Numeric(p,d)
– Real, double
– float(n)
– Date
– time
Cú pháp
• Tạo bảng
CREATE TABLE tab(
col1 type1(size1)[NOT NULL], ...,
col2 type2(size2)[NOT NULL], ...,
.....
62
[CONSTRAINT <constraint 
type> clause]
...
);
• Xoá bảng
DROP TABLE tab
Tạo bảng - Ví dụ:
CREATE TABLE Supplier(
sid char(4) NOT NULL,
sname varchar(30) NOT NULL,
i lli
63
s ze sma nt,
city varchar(20), 
CONSTRAINT KhoachinhS primary key(sid)
);
CREATE TABLE SupplyProduct(
sid char(4) NOT NULL,
pid char(4) NOT NULL,
quantity smallint, 
Tạo bảng - Ví dụ (tiếp)
64
primary key(sid,pid),
foreign key(sid) references Supplier(sid),
foreign key(pid) references Product(pid),
check(quantity >0)
);
Kiểu ràng buộc
• Ràng buộc toàn vẹn (RBTV) về giá
trị miền
CONSTRAINT 
CHECK diti
65
• 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á
66
ALTER TABLE 
DROP COLUMN ; 
• Sửa
ALTER TABLE 
CHANGE COLUMN TO ;
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
12
Ví dụ:
• ALTER TABLE SupplyProduct ADD 
COLUMN price real NOT NULL;
• ALTER TABLE SupplyProduct DROP 
COLUMN price;
67
• ALTER TABLE Supplier CHANGE 
COLUMN sname TO varchar(20);
Thêm/xóa các ràng buộc
•Thêm
ALTER TABLE 
ADD CONSTRAINT 
ể
68
•Xóa
ALTER TABLE 
DROP CONSTRAINT 
Ngôn ngữ truy vấn dữ liệu
SELECT [DISTINCT] |*||
FROM 
• Cú pháp câu lệnh SQL:
69
[WHERE ]
[GROUP BY [HAVING ]]
[ORDER BY [ASC|DESC]]
[UNION |INTERSECT| MINUS <Câu truy vấn 
khác>]
Truy vấn không điều kiện 
trên một bảng
• Tìm thông tin từ các cột của bảng
SELECT ColumnName, ColumnName, ...
FROM TableName; 
SELECT * 
FROM TableName; 
70
• Ví dụ
SELECT Name
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 không điều kiện trên một bảng
Một số ví dụ khác:
• Đưa ra tên của các mặt hàng
SELECT pname FROM Product;
• Đưa ra tên khác nhau của các mặt hàng
SELECT DISTINCT pname
FROM Product;
71
• Đưa ra toàn bộ thông tin về các hãng cung ứng
SELECT * FROM Supplier;
• Đưa ra mã số hãng cung ứng, mã mặt hàng được 
cung ứng và 10 lần số lượng mặt hàng đã được 
cung ứng
SELECT sid, pid, quantity*10
FROM SupplyProduct;
Truy vấn có điều kiện trên 1 bảng
• Chọn các bản ghi (dòng)
SELECT ColumnName,ColumnName, ... 
FROM TableName
WHERE condition_expression; 
• Ví dụ
SELECT *
72
FROM Student
WHERE suburb=‘‘Bundoora’’ ;
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 
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
13
Truy vấn có điều kiện trên 1 bảng
Một số ví dụ khác:
• Đưa ra tên của các hãng cung ứng có
trụ sở tại London
SELECT sname FROM Supplier
WHERE city = ‘London’;
73
• Đưa ra mã số và tên của các hãng
cung ứng nằm ở London và có số
nhân viên lớn hơn 75
SELECT sid, sname FROM Supplier
WHERE city = ‘London’ AND size > 75;
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
– Kiểu dữ liệu số
74
• attr BETWEEN val1 AND val2 ( (attr>=val1) and 
(attr<=val2) )
• attr IN (val1, val2, ...) ( (attr=val1) or (attr=val2) or ... )
– Kiểu dữ liệu xâu
• LIKE: sử dụng đối sánh mẫu xâu với các ký tự % hoặc
_,? (thay thế cho 1 ký tự bất kỳ), * hay % (thay thế
cho 1 xâu ký tự bất kỳ)
Biểu diễn điều kiện lựa chọn -
Ví dụ:
• Đưa ra thông tin của các hãng cung ứng có số
nhân viên trong khoảng từ 100 đến 150
SELECT * FROM Supplier 
WHERE size BETWEEN 100 AND 150;
• Đưa ra mã số của hãng cung ứng mặt hàng P1
h ặ P2
75
o c
– Cách 1:
SELECT sid FROM SupplyProduct
WHERE pid = ‘P1’ OR pid = ‘P2’;
– Cách 2:
SELECT sid FROM SupplyProduct
WHERE pid IN (‘P1’, ‘P2’);
Biểu diễn điều kiện lựa chọn -
Ví dụ (tiếp)
• Đưa ra thông tin của hãng sản xuất 
có trụ sở đặt tại thành phố bắt đầu 
bằng chữ New
SELECT * FROM SUPPLIER
76
WHERE city LIKE ‘New%’;
Truy vấn có sử dụng phép toán đổi tên
• SQL cho phép đổi tên các bảng và các cột
trong một câu truy vấn (sau mệnh đề
SELECT và FROM) sử dụng cấu trúc:
• AS 
–Đưa ra tên và số nhân viên của các hãng
cung ứng ở Paris
77
SELECT sname AS HangOParis, size AS SoNhanVien
FROM Supplier
WHERE city = ‘Paris’;
SELECT SID , Stud.Name as SName, 
Sub.Name as Subject 
FROM Student as Stud,Takes, 
Subject as Sub
WHERE (Id=SID) and (SNO = No)
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), 
78
tên sinh viên (Name), thành phố (Suburb), 
mã khoá học (Course) mà các sinh viên đã đăng ký
SELECT Id, Name, Suburb,Course
FROM Student,Enrol
WHERE Id=SID
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
14
Truy vấn phức tạp trên nhiều bảng
Một số ví dụ khác:
• Đưa ra tên của hãng có cung ứng mặt
hàng P1
SELECT sname
FROM Supplier S, SupplyProduct SP
WHERE S sid = SP sid AND SP pid = ‘P1’;
79
. . .
• Đưa ra tên và mã số của hãng cung ứng ít
nhất một mặt hàng màu đỏ
SELECT sname, sid
FROM Supplier S, SupplyProduct SP, Product P
WHERE S.sid = SP.sid AND P.pid = SP.pid AND
P.colour = ‘red’;
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
80
(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
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ự cho trước
SELECT , , 
FROM ,, 
[WHERE ]
81
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
SELECT Name
FROM Student
ORDER BY Name ASC
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, , ]
Cột đượ hỉ t ệ h đề G B đượ ử
82
• c c ra rong m n roup y c s
dụng làm cơ sở để chia nhóm. Cột này cũng bắt
buộc phải được chỉ ra trong mệnh đề Select
• Ví dụ đưa ra tên các sinh viên nhóm theo thành
phố của sinh viên đó
SELECT Suburb, 
Count(Id)
FROM
Student
GROUP BY
Suburb
SELECT
Suburb, Name
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 
83
• Ví dụ: đưa ra tên các thành phố có nhiều 
hơn 3 sinh viên
SELECT Suburb, COUNT(ID)
FROM Student
GROUP BY Suburb
HAVING COUNT(ID) > 3
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
FROM Student, Takes, Subject
S d d k S d k S O S bj
84
WHERE tu ent.I = Ta es. ID an Ta es. N = u ect.No
• Tìm sid của hãng cung ứng đồng thời 2 mặt hàng 
P1 và P2
SELECT sid FROM SupplyProduct WHERE pid = ‘P1’
INTERSECT
SELECT sid FROM SupplyProduct WHERE pid = ‘P2’
• Tìm mã số của hãng không cung ứng mặt hàng 
nào
SELECT sid FROM Supplier
MINUS
SELECT sid FROM SupplyProduct
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
15
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 để
– Kiểm tra thành viên tập hợp (IN, NOT IN)
– So sánh tập hợp (>ALL, >=ALL, <ALL,<=ALL,=ALL, NOT 
IN SOME )
85
, ,
• vd:SELECT *
FROM Supplier
WHERE SIZE>=ALL(SELECT SIZE FROM Supplier);
– Kiểm tra các bảng rỗng (EXISTS hoặc NOT EXISTS)
• Các truy vấn con lồng nhau thông qua mệnh đề
WHERE
Các câu truy vấn lồng nhau (tiếp)
• Kiểm tra thành viên tập hợp với IN 
và NOT IN:
–Đưa ra mã số của các hãng cung ứng 
đồng thời 2 mặt hàng P1 và P2:
SELECT DISTINCT sid FROM SupplyProduct
86
WHERE pid = ‘P1’ AND sid IN (SELECT sid FROM 
SupplyProduct SP2 WHERE SP2.pid = ‘P2’);
–Đưa ra sid của các hãng không cung 
ứng mặt hàng P3:
SELECT sid FROM SupplyProduct
WHERE sid NOT IN (SELECT sid From 
SupplyProduct SP2 WHERE SP2.pid = ‘P3’);
Các câu truy vấn lồng nhau (tiếp)
• So sánh tập hợp: Sử dụng các phép toán 
, ≥,≤,=,≠ kèm với các mệnh đề ANY 
và ALL
– Đưa ra tên của các hãng có số nhân viên đông nhất:
SELECT sname FROM Supplier 
87
WHERE size ≥ ALL(SELECT size FROM Supplier)
– Đưa ra sid của hãng cung ứng một mặt hàng 
với số lượng bằng ít nhất 1 trong số lượng 
các mặt hàng được cung ứng bởi S2
SELECT sid FROM SupplyProduct
WHERE sid ≠ ‘S2’ AND quantity = ANY(SELECT 
quantity FROM SupplyProduct SP2 WHERE 
SP2.sid = ‘S2’);
Các câu truy vấn lồng nhau (tiếp)
• Kiểm tra tập hợp rỗng với EXISTS và 
NOT EXISTS
–EXISTS(câu truy vấn con): nhận giá trị 
đúng khi câu truy vấn con cho ra kết 
88
quả là một quan hệ khác rỗng
–NOT EXISTS(câu truy vấn con): nhận 
giá trị đúng khi câu truy vấn con cho ra 
kết quả là một quan hệ rỗng
Các câu truy vấn lồng nhau (tiếp)
• Đưa ra thông tin của các nhà cung cấp đã 
cung ứng ít nhất một mặt hàng
SELECT * FROM Supplier S
WHERE EXISTS (SELECT sid FROM 
89
SupplyProduct SP WHERE S.sid = SP.sid);
• Đưa ra thông tin của các nhà cung cấp 
không cung ứng mặt hàng nào
SELECT * FROM Supplier S
WHERE NOT EXISTS (SELECT * FROM 
SupplyProduct SP WHERE S.sid = SP.sid);
Các hàm thư viện
• Hàm tính toán trên nhóm các bản ghi
– MAX/MIN
– SUM
– AVG
– COUNT
Hà tí h t á t ê bả hi
90
• m n o n r n n g
– Hàm toán học: ABS, SQRT, LOG, EXP, SIGN, 
ROUND
– Hàm xử lý xâu ký tự: LEN, LEFT, RIGHT, MID
– Hàm xử lý thời gian: DATE, DAY, MONTH, 
YEAR, HOUR, MINUTE, SECOND
– Hàm chuyển đổi kiểu giá trị: FORMAT
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
16
Một số ví dụ với các hàm thư viện
• Có bao nhiêu mặt hàng khác nhau được cung ứng
SELECT COUNT(DISTINCT pid)
FROM SupplyProduct;
• Có tổng cộng bao nhiêu nhân viên làm cho các 
hãng ở Paris
91
SELECT SUM(size) FROM Supplier
WHERE city = ‘Paris’;
• Đưa ra số lượng mặt hàng trung bình mà hãng 
S1 cung ứng
SELECT AVG(quantity)
FROM SupplyProduct
WHERE sid = ‘S1’;
Một số truy vấn phức tạp
• Đưa ra tên của hãng S1 và tổng số mặt hàng mà hãng đó cung ứng
SELECT sname, SUM(quantity)
FROM Supplier S, SupplyProduct SP
WHERE S.sid = SP.sid AND S.sid = ‘S1’
GROUP BY sname;
• Đưa ra mã số các hãng cung ứng và số lượng trung bình các mặt
92
hàng được cung ứng bởi từng hãng
SELECT sid, AVG(quantity) FROM SupplyProduct
GROUP BY sid;
• Đưa ra mã số các hãng cung ứng mà số lượng mặt hàng trung
bình được cung cấp bởi hãng đó là trong khoảng từ 75 đến 100
SELECT sid, AVG(quantity) FROM SupplyProduct
GROUP BY sid HAVING AVG(quantity) BETWEEN 75 AND 100
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 
93
, , 
FROM tab1, tab2, 
WHERE 
• Ví dụ
INSERT INTO Student(Id, Name, Suburb)
VALUES (‘‘1179’’,‘‘David’’,‘‘Evr’’)
Các câu lệnh cập nhật dữ liệu
• Xóa dữ liệu:
DELETE FROM 
WHERE ;
Ví d
94
• ụ:
DELETE FROM SupplyProduct 
WHERE sid = ‘S4’;
DELETE FROM Student
WHERE Suburb = ‘‘Bundoora’’;
Các câu lệnh cập nhật dữ liệu
• Sửa đổi dữ liệu:
– UPDATE SET ( = Giá trị mới , ) 
[WHERE ];
• Ví dụ:
– Hãng S1 chuyển tới Milan
UPDATE S li SET i ‘Mil ’
95
upp er c ty = an
WHERE sid = ‘S1’;
– Tất cả các mặt hàng được cung cấp với số lượng nhỏ hơn 
100 đều tăng số lượng lên 1.5 lần
UPDATE SupplyProduct SET quantity = quantity * 1.5
WHERE quantity < 100;
96
CuuDuongThanCong.com https://fb.com/tailieudientucntt
1/30/2012
17
Lời hay ý đẹp
"Người kém thông minh nhưng say
sưa với công việc, tiến mạnh và xa
hơn người cực thông minh mà lãnh
97
đạm với công việc".
J. Deval
CuuDuongThanCong.com https://fb.com/tailieudientucntt