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
22/09/2019
Nội dung
Ngôn ngữ định nghĩa
và thao tác dữ liệu đối
với mô hình quan hệ
• Các cách tiếp cận đối với thiết kế
ngôn ngữ của CSDL quan hệ
–Giới thiệu một số ngôn ngữ và phân loại
So sánh và đánh giá
Nguyễn Hồng Phương
• Một số ngôn ngữ dữ liệu mức cao
–QBE (Query By Example)
–SQL (Structured Query Language)
Bộ môn Hệ thống thông tin
Viện Công nghệ thông tin và Truyền thông
Đại học Bách Khoa Hà Nội
• Kết luận
1
2
CSDL ví dụ 2
CSDL ví dụ 1
Supplier
SID
SNAME
SIZE
100
CITY
London
Paris
Enrol
SID
S1 Dustin
S2 Rusty
S3 Lubber
S4 M&M
S5 MBI
Student
Id
Takes
SID
SupplyProduct
70
Name
Robert
Glen
Suburb
Kew
SNO
21
Course
SID
S1
S1
S1
S2
S2
S3
S3
S3
S4
S5
PID
P1
P2
P4
P3
P4
P1
P2
P6
P1
P2
QUANTITY
500
120
60
London
NewYork
NewOrlean
London
1108
3936
8507
8452
1108
1108
8507
8507
3936
1108
8507
101
113
101
Bundoora
23
400
1000
150
Norman Bundoora
23
100
S6 Panda
Mary
Balwyn
29
250
50
Product
PID
300
Subject
Course
No
PNAME
COLOR
red
350
Name
BCS
Dept
No
21
23
29
18
Name
Dept
P1
P2
P3
P4
P5
P6
Screw
Screw
200
113
CSCE
CSCE
Systems
Database
VB
CSCE
CSCE
CSCE
Maths
green
red
10
101
MCS
Nut
200
Bolt
blue
Algebra
Plier
green
blue
3
4
Scissors
1
22/09/2019
Câu hỏi (tiếp)
Đặt vấn đề: các câu hỏi
Student
Id
Name
Robert
Glen
Suburb
Kew
• Tìm các sinh viên
đăng ký khoá học
có mã số 113
• Tìm tên của các sinh
Student
1108
3936
8507
8452
Bundoora
viên nào sống ở
Bundoora
– Tìm các bộ của bảng
Student có Suburb =
Bundoora
Id
Name
Robert
Glen
Suburb
Kew
Norman Bundoora
Mary Balwyn
1108
3936
8507
8452
Enrol
– Tìm các giá trị SID
trong bảng Enrol có
Course tương ứng
là 113
– Đưa các bộ của
bảng Student có
SID trong các giá
trị tìm thấy ở trên
Bundoora
Norman Bundoora
Mary Balwyn
SID
Course
101
3936
1108
8507
113
– Đưa ra các giá trị của
thuộc tính Name của
các bộ này
101
Course
No
113
101
Name
BCS
Dept
CSCE
CSCE
5
MCS
6
Phân loại các ngôn ngữ truy vấn
• Ngôn ngữ đại số
– 1 câu hỏi = 1 tập các phép toán trên các quan
hệ
– Được biểu diễn bởi một biểu thức đại số (quan
hệ)
Ngôn ngữ đại số quan hệ
• Ngôn ngữ tính toán vị từ
– 1 câu hỏi = 1 mô tả của các bộ mong muốn
– Được đặc tả bởi một vị từ mà các bộ phải thoả
mãn
– Phân biệt 2 lớp:
• 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
7
8
2
22/09/2019
Phân loại các phép toán đại số quan hệ
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
• Phép toán quan hệ
– Phép chiếu (projection)
– Phép chọn (selection)
– Phép kết nối (join)
– Đầu vào: một hay nhiều quan hệ
– Đầu ra: một quan hệ
– Phép chia (division)
• Biểu thức đại số quan hệ = chuỗi các
phép toán
• Phép toán tập hợp
– Phép hợp (union)
– Phép giao (intersection)
– Phép trừ (difference)
• Kết quả thực hiện một biểu thức đại số là
một quan hệ
– Phép tích đề-các (cartesian product)
• Được cài đặt trong phần lớn các hệ CSDL
hiện nay
9
10
Phép hợp
Phép toán tập hợp
• Đ/n: gồm các bộ thuộc ít nhất 1 trong 2
quan hệ đầu vào
• Định nghĩa: Quan hệ khả hợp
–2 quan hệ r và s được gọi là khả hợp
nếu chúng được xác định trên cùng 1
miền giá trị
–r xác định trên D1x D2 x…x Dn
–s xác định trên D’1x D’2 x…x D’m
– Di = D’i và n=m
• 2 quan hệ đầu vào phải là khả hợp
• Cú pháp: R = R1 R2
R1
R2
R1
R2
Kết quả
Subject1
Subject2
Name
Systems
Course
BCS
Name
Systems
Course
BCS
Name
DataMining
Writing
Course
MCS
Database
Database
Algebra
BCS
Database
Database
Algebra
BCS
MCS
MCS
MCS
BCS
MCS
MCS
DataMining
Writing
11
BCS
3
22/09/2019
Phép giao
Phép trừ
• Đ/n: gồm các bộ thuộc cả hai quan
hệ đầu vào
• Cú pháp: R1 R2
• Đ/n: gồm các bộ thuộc quan hệ thứ nhất nhưng
không thuộc quan hệ thứ hai
– 2 quan hệ phải là khả hợp
• Cú pháp: R1 \ R2 hoặc R1 - R2
R1
R2
R1
R2
R1
R1
\
R2
R2
Subject1
Subject2
Subject1
Subject2
Kết quả
Kết quả
Name
Systems
Course
Name
DataMining
Database
Systems
Course
MCS
MCS
BCS
Name
Systems
Course
Name
DataMining
Database
Systems
Course
MCS
MCS
BCS
Name
Systems
Database
Course
BCS
BCS
BCS
Name
Database
Algebra
Course
BCS
BCS
BCS
Database
Database
Algebra
Database
Database
Algebra
\
MCS
MCS
MCS
MCS
MCS
MCS
Writing
BCS
Writing
BCS
13
14
Phép tích Đề-các
Ví dụ phép tích Đề-các
Student
Sport
SportID
• Đ/n: là kết nối giữa từng bộ của
quan hệ thứ nhất với mỗi bộ của
quan hệ thứ hai
Id
Name
Suburb
Kew
Sport
1108
3936
Robert
Glen
05
Swimming
Dancing
X
Bundoora
09
8507 Norman Bundoora
• Cú pháp: R = R1 x R2
8452
Mary
Balwyn
Student_Sport
Id
Name
Suburb
SportID
05
Sport
1108
1108
3936
3936
8507
8507
8452
8452
Robert
Robert
Glen
Kew
Swimming
Dancing
Swimming
Dancing
Swimming
Dancing
Swimming
Dancing
a
a
b
b
c
c
d
d
x
y
x
y
x
y
x
y
Kew
09
a
b
x
y
Bundoora
Bundoora
05
X
c
d
Glen
09
Norman Bundoora
Norman Bundoora
05
09
Mary
Mary
Balwyn
Balwyn
05
15
16
09
4
22/09/2019
Phép chiếu
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.
• Đ/n: Lựa chọn một số thuộc tính từ một quan hệ.
condition (R)
• Cú pháp:
A1,A2,... (R)
• Cú pháp:
R1
R2
R3
R2
R3
R4
C1 C2 C3 C4 C5
C2 C5
Ví dụ: đưa ra danh sách tên của tất cả các sinh
viên
• Ví dụ: đưa ra danh sách những sinh viên
sống ở Bundoora
suburb'Bundoora' (Student)
Student
Id
Name (Student)
Student
Name
Robert
Glen
Suburb
Id
Name
Robert
Glen
Suburb
Kew
Name
Robert
Glen
Id
Name
Suburb
1108
3936
8507
8452
Kew
1108
3936
8507
8452
3936
Glen
Bundoora
Bundoora
Bundoora
8507 Norman Bundoora
Norman Bundoora
Mary Balwyn
Norman Bundoora
Mary Balwyn
Norman
Mary
17
18
Ví dụ: chọn và chiếu
Phép chọn - Điều kiện ?
• Điều kiện chọn còn gọi là biểu thức
chọn.
• Đưa ra tên của các sinh viên sống ở
Bundoora
• Biểu thức chọn F: một tổ hợp logic
của các toán hạng. Mỗi toán hạng là
một phép so sánh đơn giản giữa 2
biến là hai thuộc tính hoặc giữa 1
biến là 1 thuộc tính và 1 giá trị hằng.
–Các phép so sánh trong F: , , ,, ,
–Các phép toán logic trong F: , ,
Name ( suburb'Bundoora 'Student)
Student
Id
Name
Robert
Glen
Suburb
Kew
Name
Glen
1108
3936
8507
8452
Bundoora
Bundoora
Balwyn
Norman
Norman
Mary
19
20
5
22/09/2019
Phép kết nối (join) 2 quan hệ r và s
Phép kết nối - Ví dụ:
• Khái niệm ghép bộ: u = (a1,..,an);v=(b1,..,bm)
(u,v) = (a1,..,an,b1,..,bm)
• Phép kết nối 2 quan hệ thực chất là phép ghép các
cặp bộ của 2 quan hệ thỏa mãn 1 điều kiện nào đó
trên chúng.
• Biểu thức kết nối là phép hội của các toán hạng,
mỗi toán hạng là 1 phép so sánh đơn giản giữa 1
thuộc tính của quan hệ r và 1 thuộc tính của quan
hệ s.
• Đưa ra danh sách các sinh viên và
mã khoá học mà sinh viên đó tham
gia: Student ⋈Id=SID Enrol
Student
Enrol
SID
Id
Name
Suburb
Kew
Course
101
3936
1108
8507
1108 Robert
3936 Glen
Id=SID
113
Bundoora
101
8507 Norman Bundoora
8452
Mary
Balwyn
SID
Id
Name
Suburb
Course
• Cú pháp: R1 ⋈<<điều kiện>> R2
Kết quả
1108 1108 Robert
3936 3936 Glen
Kew
113
101
101
Bundoora
8507 8507 Norman Bundoora
21
22
Phép kết nối bằng-kết nối tự nhiên
Phép kết nối tự nhiên - Ví dụ:
• Định nghĩa: Nếu phép so sánh trong
điều kiện kết nối là phép so sánh
bằng thì kết nối gọi là kết nối bằng
• Định nghĩa: Phép kết nối bằng trên
các thuộc tính cùng tên của 2 quan
hệ và sau khi kết nối 1 thuộc tính
trong 1 cặp thuộc tính trùng tên đó
sẽ bị loại khỏi quan hệ kết quả thì
phép kết nối gọi là kết nối tự nhiên
Takes
SID
Enrol
SID
SID
SNO Course
SNO
21
Course
101
1108
1108
8507
8507
21
23
23
29
113
113
101
101
1108
1108
8507
8507
3936
1108
8507
*
23
113
23
101
29
• Cú pháp phép kết nối tự nhiên: R1 *
R2
23
24
6
22/09/2019
Phép kết nối ngoài
Ví dụ: chọn, chiếu, kết nối
• Đưa ra tên của các sinh viên sống ở
Bundoora và mã khoá học mà sinh viên đó
đăng ký:
• Phép kết nối ngoài trái
a r
b r
c v
r x
s y
t z
a r x
Name,Course ( Suburb'Bundoora ' (Student Id SID Enrol ))
b r x
null
c v
Id
Name
Robert
Glen
Suburb
Kew
Student
Enrol
1108
3936
Bundoora
Kết quả
• Phép kết nối ngoài phải
8507 Norman Bundoora
Name
Course
101
8452
Mary
Balwyn
Glen
a r
b r
c v
r x
s y
t z
a r x
b r x
Norman
101
SID
3936
1108
8507
Course
101
null
s y
t z
null
113
25
26
101
Phép chia
Phép kết nối ngoài - Ví dụ:
• Đưa ra danh sách các sinh viên và mã
khoá học mà sinh viên đó đăng ký nếu có
• Định nghĩa: Phép chia giữa 1 quan
hệ r bậc n và quan hệ s bậc m
(m<n) với sơ đồ quan hệ của s là tập
con của sơ đồ quan hệ của r là một
tập các (n-m)-bộ sao cho khi ghép
mọi bộ thuộc s với t thì ta đều có
một bộ thuộc r
Student
ID
Enrol
SID
Name
Robert
Glen
Suburb
Kew
Course
101
1108
3936
8507
8452
3936
1108
8507
Bundoora
113
Norman Bundoora
101
Mary
Balwyn
ID
Name
Robert
Glen
Suburb Course
• Cú pháp: R = R1 : R2
Kết quả
1108
3936
8507
8452
Kew
113
101
101
null
Bundoora
Norman Bundoora
Mary Balwyn
27
28
7
22/09/2019
Luyện tập
Phép chia (tiếp)
a
a
a
b
c
x
y
z
x
y
• Phép hợp (Union)
x
z
a
:
• Ví dụ: Đưa ra môn học được dạy ở
tất cả các khoá học
Ví dụ:
Subject
Course
Kết quả
Name
Systems
Course
BCS
Course
BCS
Name
:
Database
Database
Database
Algebra
BCS
MCS
MCS
MCS
29
30
Luyện tập
Luyện tập
• Phép giao (intersection)
• Phép trừ (minus)
Ví dụ:
Ví dụ:
31
32
8
22/09/2019
Luyện tập
Luyện tập
• Phép tích Đề - Các (Cartesian Product)
• Phép chiếu (Projection)
Ví dụ:
Ví dụ:
33
34
Luyện tập
Luyện tập
• Phép kết nối (join)
• Phép chọn (Selection)
Ví dụ:
Ví dụ:
35
36
9
22/09/2019
Luyện tập
Luyện tập
• Phép chia (Division)
• Kết nối tự nhiên (natural join)
Ví dụ:
37
38
Bài tập
Yêu cầu của bài tập
• Cho CSDL gồm 3 quan hệ sau: S(Các hãng cung
ứng), P (các mặt hàng), SP(các sự cung ứng).
• Biểu diễn các truy vấn sau bằng đại số
quan hệ:
– Đưa ra danh sách các mặt hàng màu đỏ
– Cho biết S# của các hãng cung ứng mặt hàng
'P1' hoặc 'P2'
– Liệt kê S# của các hãng cung ứng cả hai mặt
hàng 'P1' và 'P2'
– Đưa ra S# của các hãng cung ứng ít nhất một
mặt hàng màu đỏ
– Đưa ra S# của các hãng cung ứng tất cả các
mặt hàng.
39
40
10
22/09/2019
Lời giải của bài tập
Bài tập về nhà
• Cho các quan hệ sau:
Supplier
SupplyProduct
sid
S1
S1
S1
S2
S3
S2
pid
P1
P2
P3
P2
P4
P3
quantity
sid sname size
city
500
400
100
200
100
155
S1
S2
Dustin 100 London
Rusty 70 Paris
S3 Lubber 120 London
Product
pid pname colour
P1 Screw
red
P2 Screw green
P3
P4
Nut
Bolt
red
blue
41
42
Bài tập về nhà
Bài tập về nhà
• Biểu diễn các truy vấn sau bằng biểu
thức đại số quan hệ:
6) Đưa ra {sid} của các hãng cung ứng
tất cả các mặt hàng màu đỏ
7) Đưa ra {sname} của các hãng có cung
ứng mặt hàng màu đỏ hoặc màu xanh
8) Đưa ra {sname} của các hãng cung
ứng ít nhất một mặt hàng màu đỏ và ít
nhất một mặt hàng màu xanh
9) Đưa ra {sid} của các hãng không cung
ứng mặt hàng nào
1) Đưa ra {sid,sname,size,city} của các
Supplier có trụ sở tại London
2) Đưa ra {pname} của tất cả các mặt
hàng
3) Đưa ra {sid} của các Supplier cung
cấp mặt hàng P1 hoặc P2
4) Đưa ra {sname} của các Supplier cung
cấp mặt hàng P3
5) Đưa ra {sname} của các hãng cung
ứng ít nhất một mặt hàng màu đỏ
43
44
11
22/09/2019
Ngôn ngữ SQL
45
46
Các thành phần của SQL
SQL (Structured Query Language)
• 1975: SEQUEL
– System-R
• 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
• 1976: SEQUEL2
– 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)
– 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
• 1978/79: SQL
– System-R
• 1986: chuẩn SQL-86
• 1989: chuẩn SQL-89
• 1992: chuẩn SQL-92
• 1996: chuẩn SQL-96
– Quyền hạn của người dùng trong khai thác CSDL
47
48
12
22/09/2019
Ngôn ngữ định nghĩa dữ liệu
Quy ước đặt tên và kiểu dữ liệu
• Quy ước đặt tên
• Các thông tin được định nghĩa bao gồm
– Sơ đồ quan hệ
– 32 ký tự: chữ cái, số, dấu _
• Kiểu dữ liệu (SQL-92)
– char(n)
– Kiểu dữ liệu hay miền giá trị của mỗi thuộc
tính
– 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
– varchar(n)
– int
– smallint
– numeric(p,d)
– real, double
– float(n)
– date
– time
– 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
49
50
Cú pháp
Tạo bảng - Ví dụ:
• Tạo bảng
CREATE TABLE Supplier(
sid char(4) NOT NULL,
sname varchar(30) NOT NULL,
size smallint,
CREATE TABLE tên-bảng(
cột-1 kiểu-dữ-liệu-1 [NOT NULL], ...,
cột-2 kiểu-dữ-liệu-2 [NOT NULL], ...,
.....
[CONSTRAINT tên-ràng-buộc kiểu-ràng-buộc]
...
);
city varchar(20),
CONSTRAINT KhoachinhS primary key(sid)
);
• Xoá bảng
DROP TABLE tên-bảng
51
52
13
22/09/2019
Tạo bảng - Ví dụ (tiếp)
Tạo bảng - Ví dụ (tiếp)
CREATE TABLE SupplyProduct(
sid char(4) NOT NULL,
pid char(4) NOT NULL,
quantity smallint,
CREATE TABLE Product(
pid char(4) NOT NULL,
pname varchar(30) NOT NULL,
colour char(8),
weight int,
city varchar(20),
CONSTRAINT KhoachinhP primary key(pid)
);
primary key(sid,pid),
foreign key(sid) references Supplier(sid),
foreign key(pid) references Product(pid),
check(quantity >0)
);
53
54
Thêm/xoá/sửa cột của các bảng
Kiểu ràng buộc
• Thêm
•Ràng buộc toàn vẹn (RBTV) về giá
trị miền
ALTER TABLE <tên bảng>
ADD COLUMN <tên cột> <kiểu dữ liệu> [NOT NULL];
CONSTRAINT <tên ràng buộc>
• Xoá
CHECK <điều kiện>
ALTER TABLE <tên bảng>
DROP COLUMN <tên cột>;
•RBTV về khoá ngoại hay phụ thuộc
tồn tại
• Sửa
ALTER TABLE <tên bảng>
CHANGE COLUMN <tên cột> TO <kiểu dữ liệu mới>;
CONSTRAINT <tên ràng buộc>
FOREIGN KEY (fki) REFERENCES tên-bảng(ki);
55
56
14
22/09/2019
Ví dụ:
Thêm/xóa các ràng buộc
• ALTER TABLE SupplyProduct ADD
COLUMN price real NOT NULL;
• ALTER TABLE SupplyProduct DROP
COLUMN price;
• ALTER TABLE Supplier CHANGE
COLUMN sname TO varchar(20);
•Thêm
ALTER TABLE <tên bảng>
ADD CONSTRAINT <tên ràng buộc>
<kiểu ràng buộc>
•Xóa
ALTER TABLE <tên bảng>
DROP CONSTRAINT <tên ràng buộc>
57
58
Truy vấn không điều kiện
trên một bảng
Ngôn ngữ truy vấn dữ liệu
•Cú pháp câu lệnh SQL:
• Tìm thông tin từ các cột của bảng
SELECT <DS cột>
FROM
SELECT
FROM
<Tên bảng>;
*
<Tên bảng>;
SELECT [DISTINCT] <DS cột>|*|<Biểu thức>|<Hàm TV>
FROM <DS bảng>
[WHERE
<Điều kiện tìm kiếm>]
• Ví dụ
[GROUP BY <DS cột> [HAVING <Điều kiện>]]
[ORDER BY <Danh sách cột> [ASC|DESC]]
[UNION |INTERSECT| MINUS <Câu truy vấn khác>]
SELECT Name
FROM
Student;
Student
Id
Name
Robert
Glen
Suburb
Kew
Name
Robert
Glen
Name (Student)
1108
3936
8507
8452
Bundoora
Bundoora
Balwyn
Robert
Mary
Mary
59
60
15
22/09/2019
Truy vấn không điều kiện trên một bảng
Một số ví dụ khác:
Truy vấn có điều kiện trên 1 bảng
• Chọn các bản ghi (dòng)
SELECT
FROM
WHERE
<DS cột>
<Tên bảng>
<Điều kiện tìm kiếm>
• Đư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
• Ví dụ
FROM Product;
SELECT
FROM
WHERE
*
• Đưa ra toàn bộ thông tin về các hãng cung ứng
SELECT * FROM Supplier;
Student
Suburb='Bundoora' ;
• Đư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
Student
Suburb'Bundoora ' (Student)
Id
Name
Suburb
1108
3936
8507
8452
Robert
Glen
Kew
Id
Name
Glen
Suburb
SELECT sid, pid, quantity*10
FROM SupplyProduct;
Bundoora
3936
8507
Bundoora
Bundoora
Robert Bundoora
Mary Balwyn
Robert
61
62
Truy vấn có điều kiện trên 1 bảng
Một số ví dụ khác:
Biểu diễn điều kiện lựa chọn
• Đưa ra tên của các hãng cung ứng có
trụ sở tại London
• 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
SELECT sname FROM Supplier
WHERE city = 'London';
– Kiểu dữ liệu số
• attr BETWEEN val1 AND val2 ( (attr>=val1) and
(attr<=val2) )
• attr IN (val1, val2, ...) ( (attr=val1) or (attr=val2) or ... )
• Đư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
– Kiểu dữ liệu xâu
SELECT sid, sname FROM Supplier
WHERE city = 'London' AND size > 75;
• 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ỳ (*, %)
(PostGreSQL sử dụng dấu % và dấu _
)
63
64
16
22/09/2019
Biểu diễn điều kiện lựa chọn -
Ví dụ:
Biểu diễn điều kiện lựa chọn -
Ví dụ (tiếp)
• Đư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
• Đư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
WHERE size BETWEEN 100 AND 150;
• Đưa ra mã số của hãng cung ứng mặt hàng P1
hoặc P2
SELECT * FROM Supplier
– Cách 1:
WHERE city LIKE 'New%';
SELECT sid FROM SupplyProduct
WHERE pid = 'P1' OR pid = 'P2';
– Cách 2:
New York, New Jersey, New Mexico, New Hampshire
SELECT sid FROM SupplyProduct
WHERE pid IN ('P1', 'P2');
65
66
Truy vấn có sử dụng phép toán đổi tên
Loại trừ các bản ghi trùng nhau
• 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:
• Từ khoá DISTINCT
SELECT DISTINCT <DS cột>
FROM <DS bảng>
• <tên cũ> AS <tên mới>
–Đưa ra tên và số nhân viên của các hãng
cung ứng ở Paris
• 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
SELECT sname AS HangOParis, size AS SoNhanVien
FROM Supplier
WHERE city = ‘Paris’;
SELECT
FROM
WHERE
SID , Stud.Name as SName,
Sub.Name as Subject
Student as Stud,Takes,
Subject as Sub
lần
SELECT DISTINCT Dept
FROM Course
(Id=SID) and (SNO = No)
67
68
17
22/09/2019
Truy vấn phức tạp trên nhiều bảng
Kết nối tự nhiên
• Điều kiện kết nối
SELECT <DS cột>
FROM A, B, C
WHERE A.CộtX = B.CộtX AND B.CộtY = C.CộtY
SELECT <DS cột>
FROM
<DS bảng>
WHERE <Điều kiện tìm kiếm>
• Ví dụ: đưa ra danh sách mã sinh viên (Id),
tên sinh viên (Name), thành phố (Suburb),
mã khoá học (Course) mà các sinh viên đã
đăng ký
SELECT <DS cột>
FROM A NATURAL JOIN B NATURAL JOIN C
SELECT Id, Name, Suburb, Course
FROM
Student, Enrol
WHERE Id=SID;
69
70
Kết nối hai bảng
Kết nối hai bảng
Kết nối ngoài trái
SELECT <DS cột>
FROM A LEFT JOIN B
ON A.Key = B.Key
Kết nối ngoài phải
SELECT <DS cột>
FROM A RIGHT JOIN B
ON A.Key = B.Key
Kết nối ngoài phải
SELECT <DS cột>
FROM A RIGHT JOIN B
ON A.Key = B.Key
Kết nối ngoài trái
SELECT <DS cột>
FROM A LEFT JOIN B
ON A.Key = B.Key
WHERE A.Key IS NULL
WHERE B.Key IS NULL
71
72
18
22/09/2019
Truy vấn phức tạp trên nhiều bảng
Kết nối hai bảng
Một số ví dụ khác:
Kết nối trong
Kết nối OUTER JOIN
SELECT <DS cột>
FROM A FULL OUTER JOIN B
ON A.Key = B.Key
SELECT <DS cột>
FROM A INNER JOIN B
ON A.Key = B.Key
• Đư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';
• Đư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, S.sid
FROM Supplier S, SupplyProduct SP, Product P
WHERE S.sid = SP.sid AND P.pid = SP.pid AND
P.colour = 'red';
Kết nối OUTER JOIN
SELECT <DS cột>
FROM A FULL OUTER JOIN B
ON A.Key = B.Key
WHERE A.Key IS NULL OR
B.Key IS NULL
73
74
Tìm kiếm có sắp xếp
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
• Sắp xếp các bản ghi kết quả theo một thứ
tự cho trước
SELECT
FROM
<DS cột>
<DS bảng>
SELECT
FROM
<DS cột>
<DS bảng>
[WHERE
<Điều kiện tìm kiếm>]
[GROUP BY <DS cột>]
[WHERE
<Điều kiện tìm kiếm>]
• Cột được chỉ ra trong mệnh đề GroupBy đượ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 đó
ORDER BY <DS cột> [ASC|DESC]
• Ví dụ: đưa ra danh sách tên các sinh viên
theo thứ tự tăng dần
SELECT
FROM
ORDER BY Name ASC
Name
Student
SELECT Suburb, Name
FROM Student
GROUP BY Suburb
SELECT Suburb, Count(Id)
FROM Student
GROUP BY Suburb
75
76
19
22/09/2019
Các phép toán tập hợp:
Đ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ị
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
SELECT
FROM
<DS cột>
<DS bảng>
MINUS
SELECT DISTINCT Subject.Name
FROM Student, Takes, Subject
WHERE Student.Id = Takes.SID and Takes.SNO = Subject.No
[WHERE
<Điều kiện tìm kiếm>]
GROUP BY <Ds cột> HAVING <Điều kiện>
• Ví dụ: đưa ra tên các thành phố có nhiều hơn
• Tìm sid của hãng cung ứng đồng thời 2 mặt hàng
P1 và P2
3 sinh viên
SELECT sid FROM SupplyProduct WHERE pid = ‘P1’
INTERSECT
SELECT sid FROM SupplyProduct WHERE pid = ‘P2’
SELECT
FROM
Suburb, COUNT(ID)
Student
• Tìm mã số của hãng không cung ứng mặt hàng
GROUP BY Suburb
HAVING COUNT(ID) > 3
nào
SELECT sid FROM Supplier
MINUS
SELECT sid FROM SupplyProduct
77
78
Các câu truy vấn lồng nhau
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:
• Là trường hợp các câu truy vấn (con) được viết
lồng nhau
–Đưa ra mã số của các hãng cung ứng
• Thường được sử dụng để
đồng thời 2 mặt hàng P1 và P2:
– 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, )
SELECT DISTINCT sid FROM SupplyProduct
WHERE pid = 'P1' AND sid IN (SELECT sid FROM
SupplyProduct SP2 WHERE SP2.pid = 'P2');
• vd:SELECT *
FROM Supplier
WHERE SIZE>=ALL(SELECT SIZE FROM Supplier);
–Đư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');
– 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
79
80
20
Tải về để xem bản đầy đủ
24 trang
26/04/2022
6200
Bạn đang xem 20 trang mẫu của tài liệu "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", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_co_so_du_lieu_chuong_3_ngon_ngu_dinh_nghia_va_thao.pdf
