C
S
D
LI U
Chư ng 5 Ngôn ng SQL
GV: Phạm Thị Bạch Hu
Email: ptbhue@fit.hcmus.edu.vn
− Chư
− Chư
− Chư
− Chư
− Chư
− Chư
− Chư
− Chư
− Chư
Nội dung môn học
ng 1 Tổng quan về CSDL
ng 2 Mô hình ER
ng 3 Mô hình quan h
ng 4 Ph‘p toán quan h
ng 5 Ngôn ng SQL
ng 6 Ph‘p tính quan h
ng 7 Ràng buộc toàn vẹn
ng 8 Tối ưu hóa câu truy vấn
ng 9 Phụ thuộc hàm và dạng chuẩn
1
Mục ti’u chư ng
− Biết cách định nghĩa CSDL.
− Biết thao tác (tìm kiếm, th’m, xóa, s a) tr’n
c s d li u.
Lược đồ CSDL
1. NHANVIEN(MANV,HONV,TENLOT,TENNV,PHAI,LUONG,
DIACHI, NGAYSINH, MA_NQL, PHG)
2. PHONGBAN (MAPB,TENPB,TRPHG,NGAYBĐ)
3. DIADIEM_PHG(MAPB, DIADIEM)
4. DEAN(MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
5. PHANCONG (MANV, MADA, THOIGIAN)
6. THANNHAN(MANV,TENTN,PHAI,NGAYSINH,QUANHE)
2
Giới thi u SQL
− SQL: Structured Query Language.
− SQL là ngôn ngữ chuẩn của nhiều HQT CSDL, gồm
các câu lệnh định nghĩa dữ liệu, truy vấn và cập
nhật dữ liệu.
− SQL sơ khai được gọi là SEQUEL (Structured
English Query Language), do IBM phát triển trong
hệ thống System R, 1974-1976.
− Gồm các phiên bản:
̇ Chuẩn SQL-86 (SQL1) do ANSI (American National
Standards Institute) và ISO (International Standards
Organization).
̇ Chuẩn SQL-92 (SQL2).
̇ Chuẩn SQL-99 (SQL3).
Phân loại
− SQL gồm 2 nhóm câu l nh:
̇ DDL: Data Definition Language: tạo cấu trúc
CSDL.
̇ DML Data Manipulation Language: thao tác trên
dữ liệu.
•
•
•
•
•
CREATE
SELECT
INSERT
UPDATE
DELETE
3
− SQL dùng:
DDL
̇ Bảng ≡ Quan hệ.
̇ Dòng ≡ Bộ.
̇ Cột ≡ Thuộc tính.
− DDL dùng l nh CREATE để:
̇
̇
̇
̇
Tạo lược đồ (scheme).
Tạo bảng (table).
Tạo khung nhìn (view).
Tạo ràng buộc toàn vẹn (assertion, trigger).
DDL - Kiểu d li u
− Kiểu số:
̇ Số nguyên: int, smallint.
̇ Số thực: float, real, decimal, numeric.
− Chuỗi ký tự:
̇ Char(n), varchar(n), text.
− Chuỗi bit:
̇ Binary, varbinary, image.
− Boolean:
̇ Bit.
− Ngày giờ:
̇ Datetime.
4
CREATE TABLE
CREATE TABLE <Tên_bảng> (
<Tên_cột> <Kiểu_dữ_liệu> [<RBTV>],
<Tên_cột> <Kiểu_dữ_liệu> [<RBTV>],
…
[<RBTV>]
)
− Ví dụ:
CREATE TABLE PHONGBAN(
MAPB
TENPB
TRPHG
NGAYBĐ
CHAR(5),
VARCHAR(30),
CHAR(5),
DATETIME)
Các ràng buộc c bản
− Một số ràng buộc:
̇
̇
̇
̇
̇
̇
̇
NOT NULL: chỉ định 1 cột không thể bằng NULL.
NULL.
Khóa chính.
Khóa ngoại.
UNIQUE: chỉ định 1 cột không nhận giá trị trùng.
DEFAULT: gán giá trị mặc định.
CHECK: kiểm tra một điều kiện nào đó.
− Đặt t’n ràng buộc:
CONSTRAINT
<Tên_RBTV>
<RBTV>
5
Ví dụ
− Ví dụ:CREATE TABLE NHANVIEN
(
MANV
CHAR(5) PRIMARY KEY,
HONV
VARCHAR(30) NOT NULL,
TENLOT
VARCHAR (30) NOT NULL,
TENNV
VARCHAR(30) NOT NULL,
PHAI
CHAR(10) CHECK PHAI IN (‘Nam’, ‘Nu’),
LUONG
INT DEFAULT (2000000),
DIACHI
VARCHAR (100),
NGAYSINH
DATETIME,
MA_NQL
CHAR(5),
PHG
CHAR(5)
FOREIGN KEY (MA_NQL) REFERENCES NHANVIEN (MANV),
FOREIGN KEY (PHG) REFERENCES PHONGBAN(MAPB))
)
− CREATE TABLE PHONGBAN(
MAPB
CHAR(5) CONSTRAINT PK_PB PRIMARY KEY,
TENPB
VARCHAR(30),
TRPHG
CHAR(5),
NGAYBĐ
DATETIME
CONSTRAINT FK_PB FOREIGN KEY (TRPHG) REFERENCES NHANVIEN (MANV)
)
ALTER TABLE
− Thay đổi cấu trúc hoặc ràng buộc của bảng.
− Gồm có: th’m/xóa/đổi kiểu d li u cột,
th’m/xóa ràng buộc.
− L nh th’m cột:
ALTER TABLE <T’n_bảng> ADD <T’n_cột>
<Kiểu_d _li u> [<RBTV>]
Ví dụ:
ALTER TABLE NHANVIEN ADD PHUCAP INT
6
− Xóa cột:
ALTER TABLE <Tên_bảng> DROP COLUMN
<Tên_cột>
Ví dụ:
ALTER TABLE NHANVIEN DROP COLUMN PHUCAP
− Thay đổi kiểu dữ liệu
ALTER TABLE <Tên_bảng> ALTER COLUMN
<Tên_cột>
<Kiểu_dữ_liệu_mới>
Ví dụ:
ALTER TABLE NHANVIEN ALTER COLUMN PHUCAP
FLOAT
− Th’m ràng buộc
ALTER TABLE <T’n_bảng> ADD
CONSTRAINT <Ten_RBTV> <RBTV>,
CONSTRAINT <Ten_RBTV> <RBTV>,
…
− Giả s bảng NHANVIEN chưa khai báo khóa
ngoại tr’n PHG:
ALTER TABLE NHANVIEN ADD CONSTRAINT
FK_NV_PB FOREIGN KEY (PHG)
REFERENCES PHONGBAN(MAPB)
7
− Xóa ràng buộc:
ALTER TABLE <T’n_bảng> DROP
<T’n_RBTV>
− Xóa ràng buộc FK_NV
ALTER TABLE NHANVIEN DROP
CONSTRAINT FK_NV_PB
− DROP TABLE <T’n_bảng>
− Ví dụ:
DROP TABLE NHANVIEN
8
Câu l nh SQL tổng quát
SELECT [DISTINCT| ALL]
{*|[biểu_thức_tr’n cột [AS t’n_mới]] [,…]}
Các bảng dùng để lấy
FROM t’n_bảng [alias] [,…]
dữ liệu
Điều kiện lọc các
[WHERE điều_ki n_1]
dòng dữ liệu cần
quan tâm
[GROUP BY ds_thuộc tính_1]
DL sẽ được gom
[HAVING điều_ki n_2]
nhóm theo giá trị các
cột này
[ORDER BY ds_thuộc_tính_2]
Dữ liệu cần truy vấn
Điều kiện lọc các
nhóm dữ liệu cần
quan tâm
Dữ liệu xuất ra được
sắp xếp theo các
thuộc tính này
Lưu ý
− Tối thiểu có SELECT-FROM, các mệnh đề còn lại
cần dùng hay không phụ thuộc vào nhu cầu truy
vấn dữ liệu.
− Thứ tự các mệnh đề trong câu truy vấn tổng quát
không thể thay đổi.
− Không phụ thuộc chữ in hay thường.
− SQL là ngôn ngữ phi thủ tục, ta chỉ cần thể hiện:
̇ cần dữ liệu gì,
̇ ở đâu và
̇ thỏa điều kiện gì.
9
Tìm tất cả dòng, tất cả cột
− Ví dụ: Cho danh sách tất cả các phòng ban.
SELECT MAPB, TENPB, TRPHG, NGAYBĐ
FROM PHONGBAN
− Dấu * đại di n cho tất cả các cột của 1
bảng.
Ví dụ tr’n có thể viết:
SELECT *
FROM PHONGBAN
Tìm tất cả dòng, vài cột
− Tư ng ứng với ph‘p chiếu (Π) của ĐSQH.
− Ví dụ: Cho danh sách gồm mã phòng ban, t’n
nhân vi’n và lư ng.
SELECT PHG, HONV, TENLOT, TENNV,
LUONG
FROM NHANVIEN
10
DISTINCT
− Ví dụ: Cho danh sách các đề án đã được
phân công.
SELECT MADA
FROM PHANCONG
− Câu tr’n cho kết quả trùng. Để loại bỏ sự
trùng lắp d li u, ta viết:
SELECT DISTINCT MADA
FROM PHANCONG
Tính toán tr’n thuộc tính
− Ví dụ: Cho danh sách gồm có 3 cột: mã nhân
vi’n, họ t’n, lư ng nếu tăng 10% giá trị lư ng
hi n tại.
SELECT MANV, HONV + ‘ ’ + TENLOT +
‘ ’ + TENNV, LUONG*1.1
FROM NHANVIEN
11
Bí danh
− Tư ng ứng với ph‘p đổi t’n thuộc tính trong
ĐSQH.
− Kết quả ví dụ 4 cho ra các cột có t’n khó
hiểu, do cách đặt t’n tự động của HQT CSDL
đối với các thuộc tính có tính toán tr’n đó.
− Ta viết như sau:
SELECT HONV, HONV + ‘ ’ + TENLOT +
‘ ’ + TENNV AS HOTEN, LUONG*1.1
AS LUONGMOI
FROM NHANVIEN
Tìm d li u thỏa điều ki n
− Điều ki n được thành lập tr’n 1 thuộc tính.
Có nh ng kiểu điều ki n như sau:
̇
1.
2.
3.
4.
5.
So sánh: =, <>, <, >, <=, >=.
Miền.
Tập hợp.
Tìm chuỗi thỏa mẫu cho trước.
Null.
Điều ki n phức được thành lập dựa tr’n
điều ki n đ n, bằng cách dùng các toán t
logic: AND, OR, NOT.
12
So sánh
− Ví dụ: Cho danh sách các nhân viên có lương nhiều
hơn 2500000.
SELECT MANV, HONV, TENLOT, TENNV, LUONG
FROM NHANVIEN
WHERE LUONG > 2500000
− Ví dụ: Cho danh sách các đề án diễn ra ở HCM
hoặc Đà Nẳng.
SELECT MADA, TENDA, DIADIEM_DA
FROM DEAN
WHERE DIADIEM_DA = ‘HCM’ OR DIADIEM =
‘Đà Nẳng’
Điều ki n li’n quan đến miền
− Ví dụ: Cho danh sách các nhân vi’n có lư ng
từ 3000000 đến 4000000.
SELECT MANV, HONV, TENLOT, TENNV, LUONG
FROM NHANVIEN
WHERE LUONG BETWEEN 300000 AND 4000000
13
Điều ki n li’n quan đến tập hợp
− Ví dụ: Cho danh sách các đề án diễn ra ở
HCM hoặc Đà Nẳng.
SELECT MADA, TENDA, DIADIEM_DA
FROM DEAN
WHERE DIADIEM_DA IN (‘HCM’, ‘Đà
Nẳng’)
Tìm chuỗi
1. % : chuỗi bất kỳ, có thể rỗng.
2. _ : ký tự đơn bất kỳ.
3. DIACHI LIKE ‘H%’: địa chỉ bắt đầu bởi chữ
H.
4. DIACHI LIKE ‘H_ _ ’: địa chỉ có đúng 3 ký
tự, bắt đầu bởi H.
5. DIACHI LIKE ‘%e’: địa chỉ là chuỗi bất kỳ
kết thúc bởi ký tự e.
6. DIACHI NOT LIKE ‘H%’: địa chỉ không bắt
đầu bởi H.
14
− Ví dụ: Cho danh sách các nhân viên ở Tp.
HCM.
SELECT MANV, HONV, TENLOT, TENNV,
DIACHI
FROM NHANVIEN
WHERE DIACHI LIKE ‘%Tp. HCM%’
Điều ki n li’n quan giá trị Null
− Ví dụ: Cho danh sách các nhân vi’n chưa
được bố trí phòng.
SELECT *
FROM NHANVIEN
WHERE PHG IS NULL
15
Sắp xếp dựa tr’n 1 cột
− Từ khóa theo sau thuộc tính dùng để sắp
xếp: ASC (sắp tăng, mặc định), DESC (sắp
giảm).
Ví dụ: Cho danh sách nhân vi’n sắp theo mã
phòng.
SELECT *
FROM NHANVIEN
ORDER BY PHG
Sắp xếp dựa tr’n nhiều cột
Ví dụ: Cho danh sách nhân vi’n sắp tăng dần
theo mã phòng, đối với từng phòng sắp theo
thứ tự lư ng giảm dần.
SELECT *
FROM NHANVIEN
ORDER BY PHG, LUONG DESC
16
Hàm tính toán
− Count:đếm số giá trị khác null của trư ng đối
số.
− Sum: tính tổng các giá trị của trư ng đối số.
− Avg: tính giá trị trung bình của trư ng đối số.
− Min: trả về giá trị nhỏ nhất tr’n trư ng đối số.
− Max: trả về giá trị lớn nhất tr’n trư ng đối số.
− Đặc điểm:
̇ Nhận đối số là 1 trường và trả về 1 giá trị.
̇ Count, min, max áp dụng cho trường kiểu số lẫn
kiểu không phải là số.
̇ Sum, avg chỉ áp dụng trên trường kiểu số.
Hàm tính toán
− Các hàm tính toán chỉ thao tác tr’n các giá trị
khác null, trừ count (*).
− Count(*) đếm số dòng của 1 bảng, dù dòng
đó có giá trị null hay giá trị trùng.
− DISTINCT dùng để loại bỏ sự trùng lặp trước
khi vận dụng hàm, nhưng DISTINCT không
có tác dụng đối với min, max.
17
Hàm tính toán
− Nếu câu SELECT có dùng hàm tính toán và
không có m nh đề GROUP BY thì không
được li t k’ m nh đề SELECT các thuộc
tính không phải là đối số của hàm tính toán
đã dùng.
− Ví dụ: Câu sau đây SAI:
SELECT PHG, COUNT(LUONG)
FROM NHANVIEN
Count()
− Ví dụ: Cho biết có tất cả bao nhi’u nhân vi’n.
SELECT COUNT (*)
FROM NHANVIEN
− Ví dụ: Cho biết có bao nhi’u nhân vi’n có
lư ng lớn h n 3000000.
SELECT COUNT(*)
FROM NHANVIEN
WHERE LUONG > 3000000
18
Count DISTINCT
− Có bao nhiêu đề án đã được phân công.
Câu SAI:
SELECT COUNT (MADA)
FROM PHANCONG
Câu đúng:
SELECT COUNT(DISTINCT MADA)
FROM PHANCONG
− Có bao nhiêu nhân viên thuộc phòng số 5 và tổng lương của
họ.
SELECT COUNT (*), SUM(LUONG)
FROM NHANVIEN
WHERE PHG = 5
− Ví dụ: Tìm lư ng thấp nhất, cao nhất và
lư ng trung bình của các nhân vi’n.
SELECT MIN (LUONG) AS THAPNHAT, MAX
(LUONG) AS CAONHAT, AVG(LUONG) AS
TRUNGBINH
FROM NHANVIEN
19
Group by
− GROUP BY được dùng để tạo ra các nhóm dữ liệu
trước khi vận dụng hàm.
− Các thuộc tính sau mệnh đề GROUP BY gọi là
thuộc tính gom nhóm.
̇ Hàm sẽ được thực hiện trên từng nhóm khi câu truy vấn
có mệnh đề GROUP BY.
̇ Mỗi thuộc tính liệt kê sau SELECT sẽ có 1 giá trị đối với
từng nhóm.
̇ Tất cả các thuộc tính sau SELECT phải xuất hiện ở mệnh
đề GROUP BY (trừ thuộc tính mang giá trị là hàm).
̇ Có thể có các thuộc tính xuất hiện ở mệnh đề GROUP
BY nhưng không xuất hiện sau SELECT.
̇ Hai dòng mang giá trị null trên thuộc tính gom nhóm sẽ
được gom thành cùng một nhóm.
̇ Thứ tự thực hiện: (1) điều kiện sau WHERE (2) GROUP
BY (3) hàm tính toán trên nhóm (4) điều kiền sau
HAVING.
Group by
− Ví dụ: Cho biết mỗi phòng ban có bao nhiêu nhân viên và
tổng lương của các nhân viên trong từng phòng.
SELECT PHG, COUNT(*), SUM (LUONG)AS TONG
FROM NHANVIEN
GROUP BY PHG
− Ví dụ: Cho biết lương trung bình của nhân viên nam và nhân
viên nữ trong phòng số 5.
SELECT PHAI, AVG(LUONG)AS TRUNGBINH
FROM NHANVIEN
WHERE PHG = 5
GROUP BY PHAI
20
Having
− Ví dụ: Cho danh sách các phòng ban có
lư ng trung bình của các nhân vi’n nam lớn
h n 4000000
SELECT PHG, AVG (LUONG)
FROM NHANVIEN
WHERE PHAI = ‘Nam’
GROUP BY PHG
HAVING AVG (LUONG) > 4000000
Câu truy vấn con
− Là câu truy vấn xuất hi n trong một câu truy
vấn khác. Kết quả của câu truy vấn con sẽ
được dùng cho m nh đề SELECT khác.
− Một câu truy vấn con có thể được dùng trong
các m nh đề: WHERE, HAVING, INSERT,
UPDATE, DELETE.
− Câu truy vấn con có thể trả về:
̇ Một giá trị, tức một dòng một cột.
̇ Nhiều dòng một cột.
̇ Nhiều dòng nhiều cột.
21
Câu truy vấn con
− Ví dụ: Cho danh sách các nhân vi’n thuộc
phòng ban t’n là Nghi’n cứu
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN
WHERE PHG =(SELECT MAPB
FROM PHONGBAN
WHERE TENPB = ‘Nghien
cuu’)
Câu truy vấn con
− Có thể dùng câu truy vấn con sau một toán
t so sánh m nh đề WHERE hoặc
HAVING.
− Ví dụ: Cho danh sách các nhân vi’n có lư ng
lớn h n lư ng trung bình của toàn bộ nhân
vi’n.
SELECT MANV,HONV,TENLOT,TENNV
FROM NHANVIEN
WHERE LUONG > (SELECT AVG(LUONG)
FROM NHANVIEN)
22
Câu truy vấn con - IN
− Ví dụ: Cho biết danh sách các nhân vi’n có
tham gia đề án.
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN
WHERE MANV IN (SELECT MANV
FROM PHANCONG)
ANY & ALL
− Nếu câu truy vấn con cho kết quả rỗng thì mệnh đề
ALL có giá trị TRUE còn mệnh đề ANY có giá trị
FALSE.
− Chuẩn ISO dùng SOME tương đương với ANY.
− Ví dụ: Cho biết nhân viên nào có lương lớn hơn ít
nhất giá trị lương bất kỳ của một nhân viên thuộc
phòng số 5.
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN
WHERE LUONG > SOME (SELECT LUONG
FROM NHANVIEN
WHERE PHG = 5)
23
− Ví dụ: Cho biết nhân viên nào có lương lớn
hơn tất cả giá trị lương của các nhân viên
thuộc phòng số 5.
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN
WHERE LUONG > ALL (SELECT LUONG
FROM NHANVIEN
WHERE PHG = 5)
Truy vấn từ nhiều bảng
− Ta có thể truy xuất d li u từ nhiều bảng.
− Ví dụ: Cho danh sách các nhân vi’n thuộc
phòng ban t’n là Nghi’n cứu .
SELECT MANV, HONV, TENLOT, TENNV
FROM NHANVIEN N, PHONGBAN P
WHERE N.PHG = P.MAPB AND TENPB =
‘Nghien cuu’
24
Kết trái (Left join)
−Ví dụ: Cho biết t’n các nhân vi’n và mã đề án
mà nhân vi’n đó có tham gia, nh ng ai không
có tham gia đề án thì thông tin đề án là NULL.
SELECT MANV, TENNV, MADA
FROM NHANVIEN NV LEFT JOIN PHANCONG
PC ON NV.MANV = PC.MANV
Tư ng tự có kết phải (Right join), và kết ngoài
(full outer join). Kết quả của ph‘p kết ngoài là
hội của kết quả ph‘p kết trái và kết phải.
Exists, not exists
− Được dùng trong câu truy vấn con, EXISTS
trả về TRUE nếu kết quả câu truy vấn con có
ít nhất 1 dòng.
− Ví dụ: Cho danh sách các nhân vi’n có tham
gia đề án.
SELECT *
FROM NHANVIEN NV
WHERE EXISTS (SELECT * FROM
PHANCONG WHERE MANV = NV.MANV)
25
− Ví dụ: Cho danh sách các nhân vi’n không
có tham gia đề án.
SELECT *
FROM NHANVIEN NV
WHERE NOT EXISTS (SELECT * FROM
PHANCONG WHERE MANV = NV.MANV)
Hội
− Ví dụ: Cho danh sách các nhân vi’n có tham
gia đề án t’n X hoặc Y .
SELECT MANV, TENNV
FROM NHANVIEN NV, PHANCONG PC, DEAN
DA
WHERE NV.MANV = PC.MANV AND
PC.MADA = DA.MADA AND (TENDA = ‘X’
OR TENDA = ‘Y’)
26
Giao
− Ví dụ: Cho danh sách các nhân viên vừa tham gia
đề án tên ‘X’ vừa tham gia đề án tên ‘Y’.
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM PHANCONG
PC1, DEAN DA1 WHERE PC1.MADA =
DA1.MADA AND TENDA = ‘X’)
AND MANV IN (SELECT MANV FROM PHANCONG
PC2, DEAN DA2 WHERE PC2.MADA =
DA2.MADA AND TENDA = ‘Y’)
Hi u
− Ví dụ: Cho danh sách các nhân viên có tham gia đề
án tên ‘X’ nhưng không có tham gia đề án tên ‘Y’.
SELECT MANV, TENNV
FROM NHANVIEN
WHERE MANV IN (SELECT MANV FROM PHANCONG
PC1, DEAN DA1 WHERE PC1.MADA =
DA1.MADA AND TENDA = ‘X’)
AND MANV NOT IN (SELECT MANV FROM
PHANCONG PC2, DEAN DA2 WHERE PC2.MADA
= DA2.MADA AND TENDA = ‘Y’)
27
Insert
− Ví dụ: Phân công nhân vi’n mã 001 làm đề
án mã là DAX trong th i gian 10 gi .
Insert into PHANCONG values (‘001’,
‘DAX’, 10)
− Ví dụ: Phân công nhân viên mã 001 làm tất
cả các đề án do phòng số 5 chủ trì.
Insert into PHANCONG (SELECT 001,
MADA, NULL FROM DEAN WHERE PHONG
= 5)
Update
− Ví dụ: Cập nhật lư ng của các trư ng phòng
tăng 10%.
UPDATE NHANVIEN
SET LUONG = LUONG*1.1
WHERE MANV IN (SELECT TRPHG FROM
PHONGBAN)
28
Delete
− Ví dụ: Xóa các phân công li’n quan đến
nhân vi’n mã là 001
DELETE PHANCONG WHERE MANV = ‘001’
29