Bài giảng Cơ sở dữ liệu - Chương 5: Ngôn ngữ truy vấn SQL

Ngôn ngữ truy vấn SQL 
Chương 5 
Nội dung trình bày 
Giới thiệu 
Định nghĩa dữ liệu 
Cập nhật dữ liệu 
Truy vấn dữ liệu 
Giới thiệu 
SQL (Structured Query Language) 
Ngôn ngữ cấp cao. 
1970, phát triển bởi IBM. 
Được chuẩn hóa bởi ANSI và ISO 
SQL-86. 
SQL-92. 
SQL-99. 
Gồm 
Ngôn ngữ định nghĩa dữ liệu. 
Ngôn ngữ thao tác dữ liệu. 
Ngôn ngữ định nghĩa khung nhìn. 
Ngôn ngữ phân quyền và bảo mật. 
Định nghĩa dữ liệu 
Ngôn ngữ 
Mô tả lược đồ cho các quan hệ. 
Mô tả miền giá trị cho các thuộc tính. 
Mô tả ràng buộc toàn vẹn. 
Chỉ mục trên mỗi quan hệ. 
Gồm các lệnh 
CREATE / DROP DATABASE 
CREATE / DROP / ALTER TABLE 
CREATE / DROP DOMAIN 
Kiểu dữ liệu (1) 
Số 
Số nguyên 
INTEGER (INT) 
SMALL INTEGER (SMALLINT) 
Số thực 
FLOAT[n] 
REAL 
DOUBLE PRECISION 
Số thập phân 
DECIMAL(p,n) 
Kiểu dữ liệu (2) 
Chuỗi ký tự 
Chuỗi có độ dài cố định 
CHARACTER(n) (CHAR(n)) 
Chuỗi có độ dài thay đổi 
CHARACTER VARYING(n) (VARCHAR(n)) 
Chuỗi ký tự unicode 
NATIONAL CHARACTER(n) 
NATIONAL CHARACTER VARYING (n) 
Giá trị mặc định n = 1. 
Chuỗi Bit 
BIT(n) 
BIT VARYING(n) 
Ngày, giờ 
DATE 
TIME 
TIMESTAMP 
Tạo và hủy CSDL 
CREATE DATABASE 
create database CsdlCongty 
DROP DATABASE [RESTRICT | CASCADE] 
drop database CsdlCongty 
Tạo bảng (1) 
CREATE TABLE ( 
	 [], 
	 [], 
	... 
	[] 
) 
RBTV 
NOT NULL 
NULL 
UNIQUE 
DEFAULT 
PRIMARY KEY 
FOREIGN KEY / REFERENCES 
CHECK 
Tạo bảng (2) 
Ví dụ 
	create table NHANVIEN ( 
	Ho	varchar(15)	not null, 
	Dem	varchar(20), 
	Ten	varchar(15)	not null, 
	MaNV	char(9)	not null, 
	Ngsinh	date, 
	Dchi	varchar(30), 
	GTinh	char, 
	Luong	decimal(10,2), 
	MaGSat	char(9), 
	MaPhong	int	not null 
	) 
Tạo bảng (3) 
Đặt tên cho RBTV 
CONSTRAINT 
Ví dụ 
	create table PHONGBAN ( 
	Ten	varchar(15)	unique, 
	MaPB	int	not null, 
	TrPhong	varchar(9)	not null, 
	NgNhanChuc	date, 
	constraint PB_PK primary key (MaPB), 
	constraint PB_TrPh foreign key (TrPhong) references NHANVIEN (MaNV) on delete set null on update cascade 
	) 
Xóa bảng 
DROP TABLE [RESTRICT | CASCADE] 
DROP TABLE 
Ví dụ 
drop table PHONGBAN 
Sửa bảng (1) 
ALTER TABLE ADD COLUMN [] 
Ví dụ 
alter table NHANVIEN add column DThoai char(10) 
ALTER TABLE DROP COLUMN 
Ví dụ 
alter table NHANVIEN drop column DThoai 
ALTER TABLE ALTER COLUMN 
Ví dụ 
alter table NHANVIEN alter column GTinh int 
Sửa bảng (2) 
ALTER TABLE ADD CONSTRAINT 
Ví dụ 
alter table NHANVIEN add constraint NV_Luong check (Luong > 0) 
ALTER TABLE DROP CONSTRAINT 
Ví dụ 
alter table NHANVIEN drop constraint NV_Luong 
Tạo và xóa miền giá trị 
CREATE DOMAIN AS 
Ví dụ 
create domain Diachi as varchar(100) 
DROP DOMAIN 
Cập nhật dữ liệu (1) 
Chèn dữ liệu 
Chèn từng dòng 
INSERT INTO [()] VALUES () 
Ví dụ 
insert into DUAN (TenDA, MaDA, Diadiem, Phong) values ('San pham X',1,'Tan Binh',5) 
insert into DUAN values ('San pham X',1,'Tan Binh',5) 
Chèn nhiều dòng 
INSERT INTO [()] 
Cập nhật dữ liệu (2) 
Xóa dữ liệu 
DELETE FROM [WHERE ] 
Ví dụ 
delete from NHANVIEN where MaPhong = 5 
delete from NHANVIEN 
Cập nhật dữ liệu (3) 
PHONGBAN 
01/01/1995 
987987987 
4 
Điều hành 
19/06/1981 
888665555 
5 
Nghiên cứu 
NgNhanChuc 
TrPhong 
MaPB 
TenPB 
Phú Nhuận 
5 
Thủ Đức 
5 
DIADIEM_PHG 
Gò Vấp 
4 
Tân Bình 
5 
Diadiem 
MaPB 
DUAN 
5 
Thủ Đức 
2 
Sản phẩm Y 
5 
Tân Bình 
1 
Sản phẩm X 
Phong 
Diadiem 
MaDA 
TenDA 
null 
null 
Cập nhật dữ liệu (4) 
Sửa dữ liệu 
UPDATE 
	SET = , 
	 = , 
	 ... 
	[WHERE ] 
Ví dụ 
update DUAN 
	set Diadiem = ‘Hanoi’, Phong = 4 
	where Maso=10	 
update NHANVIEN 
	set Luong = 1.1 * Luong 
Truy vấn dữ liệu 
Cú pháp 
SELECT 
	FROM 
	[WHERE ] 
	[GROUP BY ] 
	[HAVING ] 
	[ORDER BY ] 
Kết quả của lệnh truy vấn là một bảng. 
Bảng trong SQL có thể chứa các bộ trùng nhau. 
Trong đó 
: danh sách thuộc tính của quan hệ kết quả. 
: danh sách bảng liên quan đến câu truy vấn. 
: điều kiện để chọn hoặc ghép các bộ. 
: danh sách thuộc tính để nhóm các bộ. 
: điều kiện chọn các nhóm. 
: danh sách các thuộc tính và thứ tự sắp xếp tương ứng. 
Phép toán quan hệ 
 (R) 
	select 
	from R	 
 (R) 
	select * 
	from R 
	where 
R S 
	select * 
	from R, S 
R S 
	select * 
	from R, S 
	where 
 ( (R)) 
	select 
	from R 
	where 
Ví dụ 
Tìm các nhân viên làm việc trong phòng số 4. 
 MaPB = 4 (NHANVIEN) 
select * 
	from NHANVIEN 
	where MaPB = 4 
Cho biết họ, tên, giới tính và mức lương của các nhân viên. 
 Ho, Ten, GTinh, Luong (NHANVIEN) 
select Ho, Ten, GTinh, Luong 
	from NHANVIEN 
Cho bi ết tên các trưởng phòng 
 Ho, Ten (PHONGBAN TrPhg=MaNV NHANVIEN) 
select Ho, Ten 
	from NHANVIEN, PHONGBAN 
	where TrPhg = MaNV 
Phép toán tập hợp 
SQL hổ trợ các phép toán 
UNION (Hội) 
EXCEPT (Hiệu). 
INTERSECT (Giao). 
Đặc điểm 
Các dòng giống nhau bị loại trong bảng kết quả. 
Các bảng tham gia phép toán phải có tính khả hợp 
Giữ lại các dòng giống nhau 
UNION ALL 
EXCEPT ALL 
INTERSECT ALL 
(SELECT FROM 
	WHERE ) 
	UNION [ALL] 
	(SELECT FROM 
	WHERE ) 
(SELECT FROM 
	WHERE ) 
	EXCEPT [ALL] 
	(SELECT FROM 
	WHERE ) 
(SELECT FROM 
	WHERE ) 
	INTERSECT [ALL] 
	(SELECT FROM 
	WHERE ) 
Đặt tên 
Đặt tên trong mệnh đề SELECT: 
	select count(*) as ‘Tong so nhan vien’ 
	from NHANVIEN 
Đặt tên trong mệnh đề FROM: 
Đặt tên cho bảng 
	select NV.Ho, NV.Ten. PB.Ten 
	from NHANVIEN, PHONGBAN 
	where MaPB = MaPB 
Đặt tên cho bảng và thuộc tính 
	select * 
	from PHONGBAN AS PB(Ten, Maso, MaTrPhg, Ngay) 
select NV.Ho, NV.Ten. PB.Ten 
from NHANVIEN as NV, PHONGBAN as PB 
where NV.MaPB = PB.MaPB 
Phép toán số học 
+, –, *, / có thể áp dụng cho các giá trị số trong mệnh đề SELECT 
	select 1.1 * Luong as ‘Luong moi’ 
	from NHANVIEN 
+, - có thể áp dụng cho các giá trị kiểu ngày giờ 
	select NgNhanChuc + 150 as ‘Cong Ngay’ 
	from PHONGBAN 
NHANVIEN 
... 
Luong 
... 
40000 
30000 
38000 
25000 
Luong moi 
44000 
33000 
41800 
27500 
Phép toán so sánh và luận lý 
Dùng trong mệnh đề WHERE hoặc HAVING để xây dựng các điều kiện chọn và điều kiện kết. 
=, , ≥, 
BETWEEN AND 
AND, OR, NOT 
Tìm các nhân viên phòng số 5 có lương giữa 30.000 và 40.000 
select * 
	from NHANVIEN 
	where (Luong >= 30000) and (Luong <= 40000) and Phg = 5 
select * 
	from NHANVIEN 
	where (Luong between 30000 and 40000) and Phg = 5 
Phép toán so sánh chuỗi 
LIKE [ESCAPE ] 
Mẫu đối sánh 
Chuỗi ký tự để so sánh. 
% - thay cho một đoạn ký tự tùy ý. 
_ - thay cho một ký tự tùy ý. 
Ký tự thoát 
Ký tự để loại bỏ chức năng đặc biệt của % và _. 
Có thể dùng ký tự bất kỳ không xuất hiện trong mẫu đối sánh. 
Tìm nhân viên họ Nguyen 
select MaNV, Ho, Ten from NHANVIEN 
	where Ho LIKE ‘Nguyen%’ 
Tìm nhân viên họ Nguyen_ 
select MaNV, Ho, Ten from NHANVIEN 
	where Ho LIKE ‘Nguyen\_% 
	escape ‘\’	 
Khử các dòng giống nhau 
select Luong 
	from NHANVIEN 
SQL không tự động loại các bộ trùng nhau 
Tốn thời gian so sánh và sắp xếp. 
Sử dụng cho các truy vấn thống kê 
select distinct Luong 
	from NHANVIEN 
25000 
43000 
25000 
38000 
30000 
40000 
25000 
Luong 
43000 
25000 
38000 
30000 
40000 
Luong 
Các hàm tập hợp 
SQL cung cấp 5 hàm tập hợp: 
SUM() - tính tổng các giá trị của thuộc tính 
MAX() - tìm giá trị lớn nhất của thuộc tính 
MIN() - tìm giá trị nhỏ nhất của thuộc tính 
AVG() - tính giá trị trung bình của thuộc tính 
COUNT(*) - đếm số dòng của bảng 
COUNT() - đếm các giá trị khác null của thuộc tính 
Ví dụ 
	select sum(Luong) AS TongLuong, 
	 max(Luong) AS LuongCaonhat, 
	 min(Luong) AS LuongThapnhat, 
	 avg(Luong) AS LuongTB 
	from NHANVIEN 
Chỉ được xuất hiện trong mệnh đề SELECT hoặc HAVING 
Gom nhóm các bộ 
GROUP BY - HAVING 
	SELECT [, ] 
	FROM 
	[WHERE ] 
	GROUP BY 
	[HAVING ] 
Trong đó 
: danh sách thuộc tính gom nhóm 
: danh sách các hàm tập hợp. 
: điều kiện chọn hoặc điều kiện kết. 
: điều kiện lựa chọn các nhóm. 
Chú ý 
WHERE được thực hiện trước GROUP BY. 
HAVING chỉ xuất hiện khi có GROUP BY 
Ví dụ 
Với mỗi phòng, cho biết số dự án phòng đó điều phối. 
	select Phong, count(MaDA) as ‘So du an’ 
	from DUAN 
	group by Phong 
5 
Phú Nhuận 
3 
Sản phẩm Z 
5 
Thủ Đức 
2 
Sản phẩm Y 
4 
Gò Vấp 
10 
Tin học hóa 
4 
Gò Vấp 
30 
Phúc lợi 
DUAN 
1 
Phú Nhuận 
20 
Tái tổ chức 
5 
Tân Bình 
1 
Sản phẩm X 
Phong 
Diadiem 
MaDA 
TenDA 
Phong 
So du an 
5 
3 
4 
2 
1 
1 
Ví dụ 
Cho biết mã số, tên dự án và số nhân viên tham gia đối với những dự án có nhiều hơn 2 nhân viên tham gia. 
	select DA.MaDA, DA.Ten, count(*) as ‘So nhan vien’ 
	from DUAN as DA, THAMGIA as TG 
	WHERE DA.MaDA=TG.MaDA 
	group by DA.MaDA, DA.Ten 
	having count(*) > 2 
Gom nhóm các bộ (4) 
2 
333445555 
2 
Sản phẩm Y 
2 
453453453 
2 
Sản phẩm Y 
2 
123456789 
2 
Sản phẩm Y 
1 
453453453 
1 
Sản phẩm X 
... 
1 
123456789 
1 
Sản phẩm X 
... 
TG.MaDA 
TG.MaNV 
... 
DA.MaDA 
DA.TenDA 
2 
DA.MaDA 
3 
Sản phẩm Y 
So nhan vien 
DA.TenDA 
Sắp xếp kết quả 
ORDER BY 
SELECT 
	FROM 
	[WHERE ] 
	ORDER BY 
: danh sách các cặp (tên thuộc tính, thứ tự sắp xếp). 
Thứ tự: 
ASC - tăng dần. 
DESC - giảm dần. 
Mặc định là ASC. 
Ví dụ 
Với mỗi nhân viên, cho biết mã nhân viên và mã dự án mà nhân viên đó tham giá. Sắp xếp kết quả theo thứ tự tăng dần của mã nhân viên và giảm dần của mã dự án. 
select MaNV, MaDA 
	from THAMGIA 
	order by MaNV, MaDA desc 
MaNV 
MaDA 
123456789 
2 
123456789 
1 
333445555 
20 
333445555 
10 
333445555 
3 
333445555 
2 
... 
So sánh với NULL 
NULL 
Không biết. 
Không sẳn sàng. 
Không thể áp dụng. 
Tính toán và so sánh với NULL 
null + 3 null. 
null > 3 unknown . 
SQL cung cấp 2 phép toán 
IS NULL. 
IS NOT NULL. 
Tìm các nhân viên không có người giám sát 
	select MaNV, Ho, Ten 
	from NHANVIEN 
	where MaNQL is null 
Tìm các nhân viên có người giám sát 
	select MaNV, Ho, Ten 
	from NHANVIEN 
	where MaNQL is not null 
Logic 3 chân trị 
NOT 
TRUE 
FALSE 
FALSE 
TRUE 
UNKNOWN 
UNKNOWN 
AND 
TRUE 
FALSE 
UNKNOWN 
TRUE 
TRUE 
FALSE 
UNKNOWN 
FALSE 
FALSE 
FALSE 
FALSE 
UNKNOWN 
UNKNOWN 
FALSE 
UNKNOWN 
OR 
TRUE 
FALSE 
UNKNOWN 
TRUE 
TRUE 
TRUE 
TRUE 
FALSE 
TRUE 
FALSE 
UNKNOWN 
UNKNOWN 
TRUE 
UNKNOWN 
UNKNOWN 
Truy vấn lồng 
Truy vấn sử dụng các giá trị của truy vấn khác trong điều kiện so sánh. 
Chỉ xuất hiện trong mệnh đề WHERE. 
SELECT 
FROM 
WHERE ( 
 SELECT 
 FROM 
 WHERE )	 
Truy vấn cha 
Truy vấn con 
So sánh tập hợp (1) 
Phép toán 
IN - kiểm tra sự tồn tại của một giá trị trong một tập hợp. 
ALL - so sánh một giá trị với tất cả các giá trị của tập hợp. 
ANY - so sánh một giá trị với một giá trị nào đó của tập hợp. 
ALL, ANY được kết hợp với các phép toán so sánh {=, , , }. 
EXISTS - kiểm tra sự tồn tại của kết quả của một câu truy vấn. 
Cú pháp 
 IN 
 ALL 
 ANY 
EXISTS 
Ví dụ 
Tìm các nhân viên của phòng số 2 và 5. 
	select * 
	from NHANVIEN 
	where MaPB in (2, 5) 
Tìm các nhân viên của phòng ‘Nghiên cứu’. 
	select * 
	from NHANVIEN 
	where MaPB = ( 
	 select MaPB 
	 from PHONGBAN 
	 where TenPB = 
 ‘Nghiên cứu’) 
Tập hợp tường minh 
Truy vấn con vô hướng 
Ví dụ 
Cho biết tên nhân viên có mức lương lớn hơn lương của các nhân viên phòng số 5. 
	select * 
	from NHANVIEN 
	where Luong > ALL ( 
	 select Luong 
	 from NHANVIEN 
	 where MaPB = 5) 
Cho biết các nhân viên không tham gia các dự án mà phòng số 5 quản lý. 
	select MaNV 
	from NHANVIEN 
	where MaNV not in ( 
	 select TG.MaNV 
	 from THAMGIA as TG, DUAN as DA 
	 where TG.MaDA = DA.MaDA and DA.Phg = 5) 
Truy vấn lồng phân cấp (1) 
Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các bảng trong mệnh đề FROM của truy vấn cha. 
Truy vấn con được thực hiện trước truy vấn cha. 
Ví dụ 
	select * 
	from NHANVIEN 
	where Luong > ALL ( 
	 select Luong 
	 from NHANVIEN 
	 where MaPB = 5) 
Truy vấn lồng phân cấp (2) 
Luong 
40000 
30000 
38000 
25000 
43000 
987654321 
25000 
987987987 
25000 
999887777 
25000 
453453453 
38000 
666884444 
30000 
123456789 
40000 
333445555 
55000 
888665555 
Luong 
... 
MaNV 
NHANVIEN 
43000 
987654321 
55000 
888665555 
Luong 
... 
MaNV 
> ALL 
Truy vấn lồng tương quan (1) 
Mệnh đề WHERE của truy vấn con tham chiếu đến thuộc tính của các bảng trong mệnh đề FROM của truy vấn cha. 
Truy vấn con được thực hiện nhiều lần, mỗi lần ứng với một bộ của truy vấn cha. 
Tìm các nhân viên không có thân nhân nào 
	select * 
	from NHANVIEN as NV 
	where not exists ( 
	 select * 
	 from THANNHAN as TN 
	 where TN.MaNV = NV.MaNV) 
Truy vấn lồng tương quan (2) 
30000 
123456789 
40000 
333445555 
55000 
888665555 
Luong 
... 
MaNV 
NHANVIEN 
THANNHAN 
MaNV 
Ten 
... 
333445555 
Anh 
333445555 
The 
987654321 
An 
123456789 
Minh 
123456789 
Anh 
... 
55000 
888665555 
Luong 
MaNV 
MaNV 
Ten 
... 
MaNV 
Ten 
... 
333445555 
Anh 
333445555 
The 
MaNV 
Ten 
... 
123456789 
Minh 
123456789 
Anh 
Truy vấn lồng tương quan (3) 
10 
1 
3 
 
 
5 
2 
23 
 
10 
10 
23 
 
 
2 
7 
23 
 
1 
7 
2 
7 
D 
2 
12 
 
 
2 
12 
 
5 
1 
2 
1 
E 
C 
B 
A 
R 
3 
 
 
23 
 
 
23 
 
12 
 
 
12 
 
1 
C 
B 
A 
2 
7 
D 
5 
2 
E 
S 
 A,B,C (R) 
23 
 
1 
C 
B 
A 
R  S 
Truy vấn lồng tương quan (4) 
Biểu diễn bằng EXISTS. 
SELECT R1.A, R1.B, R1.C 
FROM R as R1 
WHERE NOT EXISTS ( 
 SELECT * 
 FROM S 
 WHERE NOT EXISTS ( 
 SELECT * 
 FROM R as R2 
 WHERE R2.D = S.D AND 
 R2.E = S.E AND 
 R2.A = R1.A AND 
 R2.B = R1.B AND 
 R2.C = R1.C)) 
Tìm các nhân viên tham gia tất cả các dự án do phòng số 5 quản lý. 
select * 
from NHANVIEN as NV 
where not exists ( 
 select * 
 from DUAN as DA 
 where DA.Phong = 5 and 
 not exists ( 
 select * 
 from THAMGIA as TG 
 where TG.MaNV = 
 NV.MaNV 
 and TG.MaDA = 
 DA.MaDA)) 
Phép kết trong SQL 
JOIN, INNER JOIN 
Dùng kết nối hai bảng trong mệnh đề FROM. 
SELECT 
	FROM ( JOIN ON ) 
Các phép kết mở rộng: 
LEFT OUTER JOIN, LEFT JOIN. 
RIGHT OUTER JOIN, RIGHT JOIN. 
FULL OUTER JOIN, FULL JOIN. 
Ví dụ 
Cho biết tên các nhân viên của phòng ‘Nghiên cứu’. 
select NV.Ho, NV.Dem, 
 NV.Ten 
from (NHANVIEN as NV join 
 PHONGBAN as PB on 
 NV.MaPB = 
 PB.MaPB) 
where PB.Ten = ‘Nghiên cứu’ 
Các phép kết có thể lồng nhau 
select DA.MaDA, PB.MaPB, 
 NV.Ten 
from ((DUAN as DA join 
 PHONGBAN as PB 
 on DA.Phong = 
 PB.Maso) 
 join NHANVIEN on 
 MaNQL = NV.MaNV) 
where Diadiem = ‘TpHCM’