Một cách lý tưởng, ngôn ngữCSDL phải cho phép người dùng:
Tạo CSDL vàcấu trúc quan hệ
Thực hiện việc xen, sửa, xóa dữliệu trên các quan hệ.
Thực hiện các câu truy vấn đơn giản vàphức tạp.
Phải thực hiện các công việc này với công sức bỏra lànhỏnhất vàcúpháp lệnh phải dễhọc.
Cóthểsửdụng như nhau trên các HQTCSDL khác nhau (~ tuân theo chuẩn nào đó).
52 trang |
Chia sẻ: tlsuongmuoi | Lượt xem: 2038 | Lượt tải: 0
Bạn đang xem trước 20 trang tài liệu Giáo trình cơ sở dữ liệu - Chương 4, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
Chương 4. NGÔN NGỮ SQL
GV: Trần Ngân Bình
tnbinh@cit.ctu.edu.vn
4. 2
Nội Dung
Giới thiệu ngôn ngữ SQL
Truy vấn dữ liệu bằng lệnh SELECT
Cập nhật dữ liệu (INSERT – Thêm, DELETE – Xóa,
UPDATE – Sửa)
4. 3
Mục tiêu của SQL
Một cách lý tưởng, ngôn ngữ CSDL phải cho phép
người dùng:
)Tạo CSDL và cấu trúc quan hệ
)Thực hiện việc xen, sửa, xóa dữ liệu trên các quan hệ.
)Thực hiện các câu truy vấn đơn giản và phức tạp.
Phải thực hiện các công việc này với công sức bỏ ra
là nhỏ nhất và cú pháp lệnh phải dễ học.
Có thể sử dụng như nhau trên các HQTCSDL khác
nhau (~ tuân theo chuẩn nào đó).
4. 4
Lịch sử phát triển của SQL
Năm 1974, D. Chamberlin (IBM San Jose Lab) định
nghĩa ngôn ngữ SEQUEL (Structured English Query
Language).
Một phiên bản sửa chữa, SEQUEL/2, đã được đưa ra
vào năm 1976 nhưng sau đó đổi tên thành SQL vì lý
do pháp lý.
Sau đó IBM tạo ra một bản mẫu HQTCSDL gọi là
System R, dựa trên SEQUEL/2.
Tuy nhiên gốc rễ của SQL là từ ngôn ngữ SQUARE
(Specifying Queries as Relational Expressions), là
ngôn ngữ đã có trước dự án System R.
Vào cuối 70s, ORACLE xuất hiện và được xem là
HQTCSDL thương mại đầu tiên dựa trên SQL.
4. 5
Lịch sử phát triển của SQL
Năm 1987, ANSI và ISO công bố chuẩn đầu tiên cho
SQL.
Năm 1989, ISO công bố thêm phần phụ lục định
nghĩa một ‘Nét cải tiến cho toàn vẹn’.
Năm 1992, phiên bản chỉnh sửa đầu tiên của chuẩn
này có mặt, mang tên SQL2 hay SQL-92.
Năm 1999, SQL:1999 được phát hành với các chuẩn
hỗ trợ cho quản lý dữ liệu hướng đối tượng.
Vào cuối năm 2003, SQL:2003 ra đời.
4. 6
Ngôn Ngữ SQL
SQL là một ngôn ngữ phi thủ tục với hai thành phần
chính:
)DDL (Data Definition Language) dùng để định nghĩa cấu trúc
của CSDL. ANSI chia DDL gồm 2 phần:
DDL gồm các lệnh để định nghĩa cấu trúc của CSDL:
CREATE TABLE, CREATE VIEW, ALTER TABLE,…
DCL (Data Control Language) gồm các lệnh để điều khiển
quyền truy cập trên dữ liệu: GRANT, REVOKE,…
)DML (Data Manipulation Language) để truy xuất và cập nhật
dữ liệu: INSERT, UPDATE, DELETE, SELECT, …
4. 7
Câu lệnh SQL
Hầu hết các phần trong câu lệnh SQL là không phân biệt chữ
hoa chữ thường, trừ các ký tự trong chuỗi dữ liệu.
Tuy câu lệnh SQL có hình thức tự do nhưng để dễ đọc, ta nên:
) Viết mỗi mệnh đề của lệnh trên một dòng riêng
) Viết bắt đầu một mệnh đề thẳng hàng với các mệnh đề khác
) Nếu một mệnh đề có nhiều vế thì nên viết mỗi vế trên một dòng và
thục vào trong mệnh đề đó.
Ví dụ:
SELECT Hten_nv, Cviec
FROM Nhan_vien
WHERE Phai = ‘nam’
AND (Ma_phong=30 OR Ma_phong=40)
Câu lệnh SQL gồm 2 thành phần: Từ khóa và từ do người dùng
định nghĩa (tên bảng, tên cột,...)
4. 8
Cú pháp câu lệnh SQL
Cú pháp của lệnh SQL được giới thiệu
theo dạng mở rộng của ký hiệu BNF:
)Chữ hoa đại diện cho từ khóa.
)Chữ thường đại diện cho các từ của người
dùng định nghĩa
)Dấu | chỉ sự lựa chọn.
)Dấu { chỉ phần tử bắt buộc phải có.
)Dấu [ chỉ phần tử tùy chọn (không bắt buộc).
)Dấu ... chỉ thành phần có thể lặp lại từ 0 đến nhiều lần.
4. 9
CSDL ví dụ
1. PHONG (MA_PHONG, TEN_PHONG, TRUONG_PHONG )
Mỗi phòng có một mã duy nhất, một tên phòng, trưởng phòng là mã nhân
viên của trưởng phòng.
2. NHANVIEN (MA_NV, HTEN_NV, PHAI, CVIEC, LUONG, PHU_CAP,
MA_PHONG)
Nhân viên có một mã duy nhất, một họ tên, phái, công việc, lương, phụ cấp
và thuộc một phòng nào đó.
3. TĐO_NN (MA_NV, NGOAINGU, BANG_CAP)
Một nhân viên có thể biết nhiều ngoại ngữ, mỗi ngoại ngữ có thể có các
bằng cấp khác nhau.
4. 10
Truy vấn đơn giản
Truy vấn DL từ một bảng, có thể định thứ tự xuất hiện
các cột
SELECT [ DISTINCT | ALL]
{ * | [, [,...] ] }
FROM
)DISTINCT: Chỉ hiển thị những dòng phân biệt.
)ALL: hiển thị tất cả (chế độ mặc định)
)Lưu ý: Khác với phép chiếu của ĐSQH, các dòng trùng nhau
của bảng kết quả không tự động bị loại bỏ.
)Dấu hoa thị (*) thay thế tất cả các cột trong bảng.
Thứ tự các mệnh đề không được thay đổi.
SELECT và FROM là hai mệnh đề duy nhất bắt buộc
phải có.
4. 11
Truy vấn đơn giản – ví dụ
Hiển thị toàn bộ nội dung của bảng
NHAN_VIEN:
SELECT * FROM Nhan_vien
Hiển thị danh sách gồm họ tên nhân viên và
phái:
SELECT HTen_nv, Phai
FROM Nhan_vien
Hiển thị danh sách các mức lương có thể có của
nhân viên:
SELECT DISTINCT Luong
FROM Nhan_vien
4. 12
Sắp xếp kết quả
Sắp xếp kết quả theo một hay nhiều cột:
)Sử dụng mệnh đề ORDER BY ở cuối lệnh SELECT:
ORDER BY [ASC| DESC]
[, [ASC| DESC],…]
ASC là chế độ sắp xếp mặc định
Ví dụ: Sắp xếp kết quả theo mã phòng tăng dần, và
lương giảm dần.
SELECT *
FROM Nhan_vien
ORDER BY Ma_phong, Luong DESC
4. 13
Chọn các dòng trong bảng (1)
Sử dụng mệnh đề WHERE sau MĐ FROM để chọn các dòng
thỏa điều kiện
WHERE [ AND | OR [...] ]
Dạng ĐK 1: So sánh giá trị thuộc tính với 1 giá trị cụ thể:
so sánh
) Ví dụ: Hiển thị tất cả các thông tin của nhân viên Lê Quỳnh Như
SELECT * FROM Nhan_vien
WHERE Hten_nv = ‘Lê Quỳnh Như’
) Ví dụ: Hiển thị tên và tên công việc của các nhân viên nam đang
làm việc trong các phòng 30, 40
SELECT Hten_nv, CVIEC
FROM Nhan_vien
WHERE Phai = ‘Nam’ AND
(Ma_phong=30 OR Ma_phong=40)
4. 14
Chọn các dòng trong bảng (2)
Dạng ĐK 2: So sánh giá trị của các thuộc tính với nhau:
so sánh
)Ví dụ: Tìm những nhân viên có lương bằng hai lần phụ cấp:
SELECT Hten_nv, Luong, Phu_cap
FROM Nhan_vien
WHERE Luong = Phu_cap * 2
Dạng ĐK 3: So sánh giá trị thuộc tính với hằng có kiểu
tương thích:
so sánh
)Ví dụ: Hiển thị họ tên các NV nam có công việc là thư ký
SELECT Hten_nv
FROM Nhan_vien
WHERE Phai = 'Nam' AND UPPER(cviec) = ‘THU KY’
4. 15
Chọn các dòng trong bảng (3)
Dạng ĐK 4: So sánh cột với một tập gồm nhiều giá trị:
[NOT] IN ()
)Ví dụ:Hiển thị họ tên các NV làm việc trong các phòng 10, 30
và 50.
SELECT Hten_nv
FROM Nhan_vien
WHERE Ma_phong IN ( 10, 30, 50 )
Dạng ĐK 5: Tìm kiếm theo phạm vi:
[NOT] BETWEEN AND
)Ví dụ: Hiển thị họ tên các NV có mức lương từ 3500 đến
4500
SELECT Hten_nv, Luong
FROM Nhan_vien
WHERE Luong BETWEEN 3500 AND 4500
4. 16
Các dạng điều kiện chọn (4)
Dạng ĐK 6: Tìm kiếm theo mẫu dạng chuỗi:
[NOT] LIKE
_ : đại diện cho một ký tự bất kỳ
% : đại diện cho một chuỗi ký tự bất kỳ
)Ví dụ: Hiển họ thị tên của các NV có tên lót là ‘Văn’
SELECT Hten_nv, Ma_phong FROM Nhan_vien
WHERE Hten_nv LIKE ‘% Van %’
)Ví dụ: Hiển thị các NV có tên vần ‘ao’ như Bao, Hao, Thao,...
SELECT * FROM Nhan_vien
WHERE Hten_nv LIKE ‘% %_ao’
Dạng ĐK 7: Tìm kiếm theo trị trống NULL
IS [NOT] NULL
)Ví dụ: Hiển thị các NV Không được hưởng phụ cấp
SELECT * FROM Nhan_vien Where Phu_cap IS NULL
4. 17
Các hàm kết tập
Các hàm kết tập (Aggregate Functions) còn được gọi
là hàm cột (Field Functions):
)AVG ()
)SUM ()
)MIN (<BT số, chuỗi hay ngày có bao hàm cột kiểu tương
ứng>)
)MAX (<BT số, chuỗi hay ngày có bao hàm cột kiểu tương
ứng>)
)COUNT ( | *)
4. 18
Các hàm kết tập – cách dùng
Hàm kết tập được dùng trong mệnh đề SELECT như
sau:
SELECT tên-hàm () [AS <Tên cột
mới>]
[,tên-hàm () [,...]]
FROM tên bảng
Lưu ý: Trong mệnh đề select của dạng này, các tên
cột phải được đặt trong hàm kết tập.
Từ khóa AS cho phép đặt lại tên cột cho các cột kết
quả
)Ví dụ: Có bao nhiêu NV trong hồ sơ nhân viên
SELECT COUNT(*) AS Tong_So_NV
FROM Nhan_vien
4. 19
Các hàm kết tập – Ví dụ
Cho biết mức lương cao nhất, thấp nhất và trung bình
SELECT MAX( Luong), MIN( Luong), AVG( Luong)
FROM Nhan_vien
Tính tổng lương phải trả cho phòng 40
SELECT SUM (Luong) FROM Nhan_vien
WHERE Ma_phong=40
Cho biết tổng số thư ký nữ và lương trung bình của họ
SELECT COUNT(*), AVG (Luong) FROM Nhan_vien
WHERE Phai = 'Nu' AND Cviec = ‘Thu Ky’
Cho biết phụ cấp thấp nhất của các công việc quản lý
SELECT MIN (Phu_cap) As PCAP_THAPNHT
FROM Nhan_vien WHERE Cviec LIKE ‘Quan Ly %‘
4. 20
Tính toán với SQL
Các toán tử này được dùng với dữ liệu loại số và bao gồm toán
tử cộng (+), trừ (-), nhân (*), chia (/).
Các toán tử này chỉ có thể được dùng trong các mệnh đề:
SELECT, HAVING và WHERE.
Ví dụ: Hiển thị họ tên, lương, phụ cấp và thu nhập hàng năm của
tất cả nhân viên, sắp xếp theo thu nhập trong năm giảm dần.
SELECT Hten_nv, Luong, Phu_cap,
(Luong + Phu_cap)*12 As Thu_nhap
FROM Nhan_vien
ORDER BY 4 DESC
Ví dụ: Hiển thị tất cả nhân viên có phụ cấp nhiều hơn 15% mức
lương.
SELECT Hten_nv, Luong, Phu_cap FROM Nhan_vien
WHERE Phu_cap > 0.15 *Luong
4. 21
Truy vấn con (1)
Trong trường hợp điều kiện chọn ở mệnh đềWHERE
cần truy cập thông tin ở các bảng khác với bảng đang
truy vấn để kiểm tra điều kiện
Ta có thể sử dụng một câu select khác lồng trong
điều kiện ở mệnh đềWHERE. Select này được gọi là
SELECT CON hay truy vấn con
4. 22
Truy vấn con (1)
Các truy vấn con sẽ nằm trong mệnh đềWHERE của
truy vấn chính.
SELECT
FROM
WHERE
( SELECT
FROM [WHERE
] )
[AND | OR
( SELECT
FROM [WHERE
] )
[ ... ] ]
Truy vấn
con 1
Truy vấn
con 2
Truy vấn
chính
(truy vấn
cha)
4. 23
Các dạng điều kiện chứa Select con (1)
Dạng 1: (): ĐK đúng khi giá
trị của cột so sánh đúng với giá trị trả về từ select con
) Ví dụ: Hiển thị họ tên nhân viên có lương cao nhất
SELECT Hten_nv FROM Nhan_vien
WHERE Luong =
( SELECT MAX (Luong) FROM Nhan_vien )
Dạng 2: ALL (): ĐK đúng khi
giá trị của cột so sánh đúng với tất cả các giá trị trả về từ
select con.
) Ví dụ: Hiển thị họ tên nhân viên có lương cao hơn tất cả các
nhân viên của phòng 30
SELECT Hten_nv FROM Nhan_vien
WHERE Luong > ALL
( SELECT Luong FROM Nhan_vien
WHERE Ma_phong =30 )
4. 24
Các dạng điều kiện chứa Select con (2)
Dạng 3: ANY|SOME (): ĐK
đúng khi giá trị của cột so sánh đúng với bất kỳ một giá
trị nào trả về từ select con.
) Ví dụ: Hiển thị họ tên trưởng phòng của tất cả các phòng
SELECT HTEN_NV FROM NHAN_VIEN
WHERE MA_NV = ANY
( SELECT TRUONG_PHONG FROM PHONG )
Dạng 4: [NOT] IN (): ĐK đúng khi giá trị
của cột nằm trong tập hợp các giá trị trả về của select con.
) Ví dụ: Hiển thị họ tên trưởng phòng của tất cả các phòng
SELECT HTEN_NV FROM NHAN_VIEN
WHERE MA_NV IN
( SELECT TRUONG_PHONG FROM PHONG )
4. 25
Các dạng điều kiện chứa Select con (3)
Dạng 5: [NOT] EXISTS (): Đk đúng khi kết quả
trả về của select con khác rỗng.
) Ví dụ: Hiển thị họ tên những nhân viên có công việc và lương
bằng với 1 nhân viên khác.
SELECT HTEN_NV FROM NHAN_VIEN AS A
WHERE EXISTS
( SELECT * FROM NHAN_VIEN AS B
WHERE A.Cviec = B.Cviec AND A.Luong = B.Luong
AND A.Ma_NV B. Ma_NV)
4. 26
Các quy luật của truy vấn con
Mệnh đề ORDER BY không được sử dụng trong truy
vấn con (mặc dù chúng có thể được dùng trong truy
vấn cha).
Mệnh đề SELECT trong truy vấn con chỉ được bao
gồm một cột duy hoặc 1 biểu thức duy nhất, ngoại trừ
truy vấn con sử dụng EXISTS.
Truy vấn con có thể truy cập các cột của các bảng ở
truy vấn cha bằng cách sử dụng bí danh
Select con trả
về:
Một cột Nhiều cột
Một dòng Dạng 1
Nhiều dòng Dạng 2, 3, 4
Dạng 5
4. 27
Gom nhóm các dòng
Đặc tính gom nhóm cho phép chúng ta thực hiện các chức năng
trên một nhóm các dòng như là một dòng riêng biệt.
Sử dụng mệnh đề GROUP BY sau mệnh đề FROM hoặc WHERE
trong lệnh select
SELECT ,
)>
FROM [ WHERE ]
GROUP BY
[, [,...]]
4. 28
Gom nhóm các dòng
Mệnh đề SELECT and GROUP BY liên quan chặt chẽ
với nhau: mỗi mục trong danh sách SELECT chỉ được
có một-giá-trị cho một nhóm.
Vì vậy, mệnh đề SELECT chỉ có thể chứa:
)Các cột phân nhóm
)Các hàm kết tập
)Các hằng
)Các biểu thức kết hợp các loại trên.
Ví dụ: Hiển thị mức lương trung bình của từng phòng.
SELECT Ma_phong, AVG (Luong)
FROM Nhan_vien
GROUP BY Ma_phong
4. 29
Điều kiện trên nhóm
Sử dụng mệnh đề HAVING theo sau mệnh đề GROUP
BY để lọc ra các nhóm theo điều kiện sau khi đã phân
nhóm: HAVING
Ví dụ: Tìm các phòng có mức lương trung bình lớn hơn
3000.
SELECT Ma_phong, AVG (Luong) FROM Nhan_vien
GROUP BY Ma_phong
HAVING AVG (Luong) > 3000
Ví dụ: Hãy hiển thị tên ngoại ngữ có số lượng người có
bằng C là trên 20 người.
SELECT Ngoai_ngu, COUNT (Ma_nv)
FROM Tđo_NN
WHERE Bang_cap = ‘C’
GROUP BY Ngoai_ngu HAVING COUNT(Ma_nv) > 20
4. 30
Thứ tự thực hiện các mệnh đề
SELECT [DISTINCT | ALL]
{* | [biểu thức cột [AS tên mới]] [,...] }
FROM Tên bảng
[WHERE điều kiện]
[GROUP BY danh sách cột] [HAVING điều kiện nhóm]
[ORDER BY danh sách cột]
FROM Lấy các bảng cần sử dụng.
WHERE Chọn các dòng thỏa điều kiện.
GROUP BY Tạo thành các nhóm dòng với cùng giá trị cột.
HAVING Chọn các nhóm thỏa điều kiện nhóm.
SELECT Chọn lấy các cột cần xuất ra
ORDER BY Sắp xếp thứ tự các dòng để xuất ra theo yêu
cầu.
4. 31
Truy vấn trên nhiều bảng
SELECT có chứa truy vấn con có thể kiểm tra điều kiện trên bảng
khác, nhưng các cột xuất ra phải từ một bảng.
Nếu muốn xuất ra các cột từ nhiều bảng thì phải thực hiện phép kết
nối (join).
Để thực hiện kết nối, liệt kê các bảng trong MĐ FROM cách nhau
bằng dấu phẩy. Thông thường dùng MĐ WHERE để mô tả các cột
kết nối.
Ví dụ: Tìm tên của phòng mà Lê Quỳnh Như đang làm việc:
SELECT NHAN_VIEN.MA_PHONG,TENPHONG
FROM NHAN_VIEN, PHONG
WHERE NHAN_VIEN.MA_PHONG = PHONG.MA_PHONG
AND HTEN_NV = ‘Lê Quỳnh Như’
Lưu ý: Giữa hai bảng có thể có số điều kiện kết nối >= 1.
Điều kiện kết nốiĐiều kiện chọn
4. 32
Đặt bí danh cho các bảng
Có thể đặt bí danh cho các bảng trong mệnh đề FROM.
Nếu đã đặt bí danh cho bảng, thì phải sử dụng nó thay cho
tên bảng trong toàn bộ lệnh select.
)Ví dụ: Cho biết họ tên nhân viên và tên phòng tương ứng mà nhân
viên đó đang làm việc.
SELECT HTEN_NV, TEN_PHONG
FROM NHAN_VIEN a, PHONG b
WHERE a.MA_PHONG = b.MA_PHONG
)Ví dụ: Cho biết họ tên và bằng cấp tương ứng của những nhân
viên có ngoại ngữ Anh văn.
SELECT HTEN_NV, BANG_CAP
FROM NHAN_VIEN a, TĐO_NN b
WHERE a.MA_NV = b.MA_NV AND Ngoai_Ngu = ‘Anh Van’
4. 33
Các cấu trúc kết nối khác
SQL cung cấp một số cách khác để mô tả phép kết
nối.
)FROM NHAN_VIEN a JOIN PHONG b ON
a.MA_PHONG = b.MA_PHONG
)FROM NHAN_VIEN JOIN PHONG USING MA_PHONG
)FROM NHAN_VIEN NATURAL JOIN PHONG
Trong mỗi trường hợp, FROM thay thế FROM and
WHERE. Tuy nhiên, trước hết phải tạo bảng với các
cột giống nhau.
4. 34
Kết nối Ngoại – Outer Join (1)
Nếu 1 dòng của 1 bảng được kết nối không khớp với
dòng nào bên bảng kia, thì dòng đó sẽ biến mất khỏi
bảng kết quả.
Kết nối ngoài cho phép giữ lại những dòng không
thỏa điều kiện kết nối.
Có 3 loại kết nối:
)FROM LEFT JOIN ON
)FROM RIGHT JOIN ON
)FROM FULL JOIN ON
4. 35
Kết nối Ngoại – Outer Join (2)
Hãy liệt kê họ tên NV, trình độ ngoại ngữ, kể cả những NV
không biết ngoại ngữ nào.
SELECT Hten_NV, Ngoai_ngu, Bang_cap
FROM Nhan_vien a LEFT JOIN Tđo_NN b
ON a.Ma_nv = b.Ma_nv
) Hoặc:
SELECT Hten_NV, Ngoai_ngu, Bang_cap
FROM Tđo_NN b RIGHT JOIN Nhan_vien a
ON a.Ma_nv = b.Ma_nv
Hãy liệt kê họ tên thư ký của từng phòng, kể cả những phòng
không có thư ký, và những thư ký chưa phân bổ cho phòng nào.
SELECT Hten_NV, Ten_Phong
FROM Nhan_vien a FULL JOIN Phong a
ON a.Ma_phong = b.Ma_phong
WHERE Cviec = ‘Thu ky’
4. 36
Cách thực hiện phép kết nối
Thủ tục để sinh ra kết quả cho phép kết nối như sau:
1. Tạo bảng kết quả tích DesCart của các bảng liệt kê trong MĐ
FROM.
2. Nếu có MĐ WHERE, thì áp dụng điều kiện tìm kiếm trên từng
dòng của bảng tích, giữ lại những dòng thỏa điều kiện.
3. Với mỗi dòng còn lại, tính giá trị cho mỗi mục trong danh sách
SELECT để tạo ra 1 dòng trong bảng kết quả.
4. Nếu có DISTINCT, thì xóa những dòng trùng lại trong KQ.
5. Nếu có ORDER BY, thì sắp xếp như yêu cầu.
SQL cung cấp 1 hình thức đặc biệt cho phép tích Descart:
SELECT [DISTINCT | ALL] {* | DS cột}
FROM CROSS JOIN
4. 37
TK có các phép toán tập hợp
Phép hợp (UNION), Giao (INTERSECT), Trừ
(Difference hay Except))
Cũng như ĐSQH, các phép toán này cũng đòi hỏi sự
tương thích giữa hai bảng. Hai thuộc tính tương thích
nếu chúng có cùng kiểu và độ rộng.
Định dạng chung của phép toán tử tập hợp là:
[ALL] [CORRESPONDING [BY {cột1 [, ...]}]]
Nếu có CORRESPONDING BY, toán tử tập hợp chỉ
thực hiện trên những cột được liệt kê.
Nếu có CORRESPONDING nhưng không có BY thì
phép toán chỉ thực hiện trên những cột chung.
Nếu có ALL, thì kết quả có thể chứa dòng trùng nhau.
4. 38
Hợp hai tập kết quả truy vấn
Ví dụ: Tìm tập hợp gồm Mã NV của các trưởng phòng
và các nhân viên có trình độ C Pháp văn.
(SELECT Truong_phong FROM Nhan_vien)
UNION
(SELECT Ma_nv FROM Tdo_NV
WHERE Ngoai_ngu = ‘Phap van’ AND Bang_cap = ‘C’)
Ví dụ: Tìm tập hợp gồm Mã NV của các thư ký và các
nhân viên có trình độ B Anh văn.
(SELECT * FROM Nhan_vien WHERE Cviec = ‘Thu ky’)
UNION CORRESPONDING By Ma_nv
(SELECT * FROM Tdo_NV
WHERE Ngoai_ngu = ‘Anh van’ AND Bang_cap = ‘B’)
4. 39
Giao hai tập kết quả truy vấn (1)
Ví dụ: Tìm các thư ký có trình độ Anh văn là B.
(SELECT * FROM Nhan_vien
WHERE Cviec = ‘Thu ky’)
INTERSECT CORRSPONDING BY Ma_nv
(SELECT * FROM Tđo_NN
WHERE Ngoai_ngu = ‘Anh van’
AND Bang_cap = ‘B’)
)Hoặc:
SELECT * FROM Nhan_vien a, Tđo_NN b
WHERE a.Ma_nv = b.Ma_nv
AND Cviec = ‘Thu ky’
AND Ngoai_ngu = ‘Anh van’
AND Bang_cap = ‘B’
Khả năng viết
một câu truy vấn
với nhiều dạng
tương đương
minh họa cho
thấy một trong
những điểm bất
lợi của SQL
4. 40
Giao hai tập kết quả truy vấn (2)
Trả lời các câu hỏi dạng giao bằng select lồng nhau
sử dụng toán tử:
) IN cho phép giao trên một cột
)EXISTS cho phép giao trên nhiều cột (hoặc mở một bảng ở
nhiều vùng).
Ví dụ:
SELECT * FROM Nhan_vien
WHERE Cviec = ‘Thu ky’
AND Ma_nv IN
(SELECT Ma_nv FROM Nhan_vien
WHERE Ngoai_ngu = ‘Anh van’
AND Bang_cap = ‘B’)
Hoặc:
SELECT * FROM Nhan_vien a
WHERE Cviec = ‘Thu ky’ AND EXISTS
(SELECT * FROM Nhan_vien b
WHERE a.Ma_nv = b.Ma_nv
AND Ngoai_ngu = ‘Anh van’
AND Bang_cap = ‘B’)
4. 41
Giao hai tập kết quả truy vấn (3)
Ví dụ: Tìm thông tin về các phòng có cả thư ký và quản
lý văn thư:
SELECT a.* FROM PHONG a, NHAN_VIEN b
WHERE a. MA_PHONG = b.MA_PHONG
AND CVIEC = ‘Quan Ly Van Thu’
AND MA_PHONG IN
(SELECT MA_PHONG FROM NHAN_VIEN
WHERE CVIEC = ‘Thu Ky’)
Đk kết nối bảng
Điều kiện chọn
Đk chứa select con
Nested Subquery
4. 42
Giao hai tập kết quả truy vấn (4)
Tìm công việc và mã phòng của các phòng có cả nhân
viên nam và nữ cùng đảm trách một công việc
SELECT Cviec, Ma_phong FROM Nhan_vien a
WHERE Phai = 'Nam' AND EXISTS
(SELECT * FROM Nhan_vien b
WHERE Phai = 'Nu' AND a.Cviec= b.Cviec
AND a.Ma_phong = b.Ma_phong)
correlated subquery
Hoặc sử dụng kỹ thuật mở bảng nhiều lần để thực hiện
các câu trên.
SELECT Cviec, Ma_phong
FROM Nhan_vien a, Nhan_vien b
WHERE a.Phai = 'Nam' AND b.Phai = 'Nu'
AND a.Cviec= b.Cviec AND a.Ma_phong = b.Ma_phong)
Câu này phải
dùng EXISTS
4. 43
Trừ hai tập kết quả truy vấn (1)
Ví dụ: Tìm những phòng không có thư ký
(SELECT * FROM Phong)
EXCEPT CORRSPONDING BY Ma_phong
(SELECT * FROM Nhan_vien
WHERE Cviec = ‘Thu ky’)
)Hoặc:
(SELECT Ma_phong FROM Phong)
EXCEPT
(SELECT Ma_phong FROM Nhan_vien
WHERE Cviec = ‘Thu ky’)
4. 44
Trừ hai tập kết quả truy vấn (2)
Có thể trả lời các câu hỏi dạng giao bằng select lồng
nhau sử dụng toán tử:
)NOT IN cho phép trừ trên một cột
)NOT EXISTS cho phép trừ trên nhiều cột.
Ví dụ:
(SELECT * FROM Phong
WHERE Ma_phong NOT IN
(SELECT Ma_phong
FROM Nhan_vien
WHERE Cviec = ‘Thu ky’)
4. 45
Cập nhật CSDL
Các lệnh cập nhật CSDL của SQL:
)Thêm dòng (INSERT)
)Xóa dòng (DELETE)
)Sửa dữ liệu trong các dòng (UPDATE)
4. 46
Thêm Dòng vào Bảng (1)
Dạng 1: cho phép xen một dòng vào bảng đã có:
INSERT INTO
[( [, [,...]])]
VALUES ( [, [,...]])
)Tên bảng ở đây có thể là bảng hoặc khung nhìn có thể cập
nhật (updatable view)
)Nếu không có danh sách cột, thì SQL ngầm hiểu là tất cả các
cột của bảng với trật tự ban đầu của nó.
)Nếu có mô tả DS cột, thì những cột nào không có mặt thì
phải là cột có thể mang trị NULL, trừ khi nó đã được định
nghĩa trị mặc định lúc tạo bảng.
)DS biểu thức và DS cột phải có cùng số phần tử và tương
thích về kiểu theo từng cặp một.
4. 47
Thêm Dòng vào Bảng (2)
Ví dụ: Thêm một dòng dữ liệu mới vào bảng
NHAN_VIEN
INSERT INTO NHAN_VIEN
(MA_NV, HTEN_NV, PHAI , MA_PHONG, CVIEC)
VALUES ('123', 'Nguyễn Văn An', 'Nam', 3,'Quan Ly Kho')
Nếu dòng thêm vào có đủ giá trị cho mỗi cột dữ liệu
thì:
INSERT INTO NHAN_VIEN
VALUES ('124', 'Nguyễn Thị Hoa', 'Nu', 'Thu Ky', 2000, 100, 5)
Chỉ có kiểu số là
không đặt trong dấu
nháy ‘’
4. 48
Thêm Dòng Vào Bảng (3)
Dạng 2: INSERT kết hợp với lệnh SELECT cho
phép chép nhiều dòng từ một hoặc nhiều bảng vào
một bảng khác.
INSERT INTO
[( [, [,...]])]
Ví dụ: Chép các nhân viên là trưởng phòng từ bảng
NHAN_VIEN vào bảng THANG_CAP:
INSERT INTO Thang_cap
SELECT Ma_nv, Hten_nv, Ma_phong
FROM Nhan_vien WHERE Cviec = ‘Truong Phong’
Tất cả các nhân viên đều có trình độ B Anh Văn. Hãy
thêm dữ liệu vào bảng TDO_NN
INSERT INTO Tdo_nn
SELECT Ma_nv, ‘AV’ , ‘B’ FROM Nhan_vien
4. 49
Sửa Dữ Liệu trong Bảng
Lệnh UPDATE cho phép sửa đổi nội dung của 1 hay
nhiều cột trên một hay nhiều dòng của một bảng.
UPDATE
SET =
[, = [,..]]
[WHERE ]
)Tên bảng ở đây có thể là bảng hoặc khung nhìn có
thể cập nhật (updatable view)
)Chỉ những cột được đề cập thì mới cập nhật
)Nếu không có WHERE thì sẽ cập nhật tất cả các
dòng.
)Giá trị của biểu thức phải có kiểu tương thích với
kiểu của cột
4. 50
Sửa Dữ Liệu trong Bảng
Ví dụ: Tăng lương cho tất cả các nhân viên thư ký
thêm 500
UPDATE NHAN_VIEN SET LUONG =
LUONG + 500
WHERE CVIEC = ‘Thu Ky’
Ví dụ: Chuyển nhân viên Lê Quỳnh Như sang phòng
15 với công việc là Trưởng Phòng
UPDATE NHAN_VIEN
SET MA_PHONG = 15, CVIEC = ‘Truong Phong’
WHERE HTEN_NV = ‘Lê Quỳnh Như’
4. 51
Xóa Dòng trong Bảng
Lệnh DELETE xóa bỏ một hay nhiều dòng trong một
bảng.
DELETE
[WHERE ]
)Tên bảng ở đây có thể là bảng hoặc khung nhìn có
thể cập nhật (updatable view)
)Nếu không có WHERE thì sẽ xóa tất cả các dòng có
trong bảng. Tuy nhiên, bảng sẽ không bị xóa.
)Phải hết sức cẩn thận khi dùng lệnh này.
4. 52
Xóa Dòng trong Bảng
Ví dụ: Nhân viên Trần Hồng đã nghỉ việc. Huỷ bỏ thông
tin về anh ta trong bảng NHAN_VIEN:
DELETE NHAN_VIEN
WHERE HTEN_NV = ‘Trần Hồng’
Ví dụ: Công ty có quyết định nhập phòng 11 vào phòng
10, hãy thực hiện các thay đổi phù hợp:
UPDATE NHAN_VIEN SET MAPHONG = 10
WHERE MA_PHONG = 11
DELETE PHONG WHERE MA_PHONG = 11
Các file đính kèm theo tài liệu này:
- Giáo trình cơ sở dữ liệu ĐH Cần Thơ (Chương 4).pdf