Slide bài giảng môn Cơ sở dữ liệu nội dung "Ngôn ngữ truy vấn SQL"

Slide bài giảng môn Cơ sở dữ liệu nội dung "Ngôn ngữ truy vấn SQL" của Học viện Công nghệ Bưu chính Viễn thông với những kiến thức và thông tin bổ ích giúp sinh viên tham khảo, ôn luyện và phục vụ nhu cầu học tập của mình cụ thể là có định hướng ôn tập, nắm vững kiến thức môn học và làm bài tốt trong những bài kiểm tra, bài tiểu luận, bài tập kết thúc học phần, từ đó học tập tốt và có kết quả cao cũng như có thể vận dụng tốt những kiến thức mình đã học vào thực tiễn cuộc sống. Mời bạn đọc đón xem!

79 40 lượt tải Tải xuống
Chia sẻ Báo cáo tài liệu

Môn: Cơ sở dữ liệu (TEL1343)

Trường: Học viện Công Nghệ Bưu Chính Viễn Thông

Thông tin:

99 trang 9 tháng trước

Tác giả:

Profile Tiến Dũng
lOMoARcPSD|36991220
Ngôn ngữ truy vấn SQL
lOMoARcPSD|36991220
Nội dung
1. Khái quát về ngôn ngữ truy vấn dữ liệu
2. Câu lệnh SELECT
3. Các hàm thao tác dữ liệu
4. Truy vấn thông tin từ nhiều bảng
5. Các lệnh cập nhật dữ liệu
6. Các lệnh liên quan tới cấu trúc
7. Các lệnh giao quyền truy cập CSDL
8. Bài tập
3.1. Giới thiệu SQL
SQL ược xem là yếu tố chính óng góp vào sự thành công của CSDL
quan hệ khi áp dụng trong thực tế.
lOMoARcPSD|36991220
Là ngôn ngữ mức cao, người dùng chỉ cần xác ịnh kết quả của truy
vấn là gì, phần còn lại là tính toán và tối ưu hoá câu lệnh ược
DBMS ảm nhiệm.
1970: SQL (Structured Query Language) bắt nguồn từ ngôn ngữ
SEQUEL (Structured English QUEry Language), ngôn ngữ ược
thiết kế tại tập oàn IBM nhằm khi ưa ra hệ quản trị CSDL có tên
là SYSTEM-R
3.1. Giới thiệu SQL (tt)
Năm 1976: SEQUEL ược cải tiến thành SEQUEL2
Năm 1978-1979: SEQUEL2 ược nâng cấp và ổi tên thành
SQL (nhưng vẫn ọc là SEQUEL)
lOMoARcPSD|36991220
Chuẩn SQL ầu tiên có tên gọi SQL-86 (SQL1) ược công nhận
và chuẩn hóa bởi ANSI và ISO.
Năm 1992: SQL1 ược mở rộng với nhiều tính năng mới và
ược gọi là SQL-92 (SQL2).
Năm 1999: Chuẩn SQL-99 (SQL3) ra ời
Phiên bản mới nhất hiện nay là SQL-2011
3.1. Giới thiệu SQL (tt)
Ngôn ngữ giao thiết CSLD gồm:
– DDL – Data Definition Language
– DML – Data Manipulation Language
– SQL – Structured Query Language
lOMoARcPSD|36991220
– DCL – Data Control Language
SQL gồm 2 nhóm lệnh
– DDL: Tạo cấu trúc CSDL
– DML: Thao tác trên CDSDL
• CREATE
• SELECT
• INSERT, UPDATE, DELETE…
Cho các lược ồ quan hệ
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI,
LUONG, DIACHI, NGAYSINH, MA_NQL, PHG)
lOMoARcPSD|36991220
PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
3.2. lệnh SELECT – Truy vấn dữ liệu
Gồm 3 mệnh ề cơ bản
SELECT <danh sách các cột>
FROM <danh sách các bảng> WHERE <
iều kiện>
lOMoARcPSD|36991220
– <danh sách các cột>
• Tên các cột cần hiển thị ở kết quả truy vấn
– <danh sách các bảng>
• Tên các bảng (nguồn) dữ liệu khi truy vấn
– < iều kiện>
• Xác ịnh biểu thức boolean xác ịnh dòng (bộ) nào ược trích ra
• Nối các biểu thức: AND, OR, NOT
• Phép toán: >, <, <=, >=, =, <>, LIKE, BETWEEN
2. lệnh SELECT (tt)
SQL và Đại số quan hệ
lOMoARcPSD|36991220
x
SELECT L
FROM
R
L
(
C
(R ))
WHERE C
SELECT <danh sách các cột>
FROM <danh sách các bảng>
WHERE < iều kiện>
lOMoARcPSD|36991220
Ví dụ
SELECT * Lấy tất cả các cột
của quan hệ
FROM NHANVIEN
WHERE PHG=5
PHG=5
(NHANVIEN)
lOMoARcPSD|36991220
Mệnh ề SELECT
lOMoARcPSD|36991220
Mệnh ề SELECT (tt)
Tên, Bí danh
SELECT MANV, HONV AS 'Họ', TENLOT AS ‘Tên lót’, TENNV AS 'Tên'
FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’
MANV,HO,TEN LOT,TEN
(
MaNV,HONV,TENLOT,TENNV PHG=5 PHAI=‘Nam’(NHANVIEN)))
lOMoARcPSD|36991220
Mệnh ề SELECT (tt)
Mở rộng:
SELECT MANV, HONV +’ ‘+ TENLOT+’ ‘ TENNV AS ‘HO TEN’
FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’
lOMoARcPSD|36991220
Mệnh ề SELECT (tt)
Mở rộng:
SELECT MANV, LUONG*1.1 AS ‘LUONG10%’
FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’
lOMoARcPSD|36991220
Mệnh ề SELECT (tt)
Loại bỏ các dòng trùng nhau
SELECT LUONG FROM NHANVIEN SELECT DISTINCT LUONG
WHERE PHG=5 AND PHAI=‘Nam’ FROM NHANVIEN
lOMoARcPSD|36991220
Mệnh ề SELECT (tt)
WHERE PHG=5 AND PHAI=‘Nam’
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
Ví dụ
Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’
SELECT MANV, TENNV
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG AND TENPGH=‘Nghiencuu’
Mệnh ề WHERE
SELECT MANV, TENNV
FROM NHANVIEN, PHONGBAN
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
WHERE PHG=MAPHG AND TENPHG=‘Nghien cuu’
TRUE TRUE
Độ ưu tiên
– Viết trước thực hiện trước
– Trong ngoặc thực hiện trước
SELECT MANV, TENNV
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
FROM NHANVIEN, PHONGBAN
WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) ANDPHG=MAPHG
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
BETWEEN
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG>=2000000 ANDLUONG<=3000000
SELECT MANV, TENNV
FROM NHANVIEN
WHERE LUONG BETWEEN 2000000 AND3000000
NOT BETWEEN
SELECT MANV, TENNV
FROM NHANVIEN
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
WHERE LUONG NOT BETWEEN 2000000 AND3000000
IN NOT IN
SELECT MANV, TENNV
FROM NHANVIEN
WHERE PHG IN (4,5)
SELECT MANV, TENNV
FROM NHANVIEN
WHERE PHG = 4 ORPHG=5
SELECT MANV, TENNV
FROM NHANVIEN
WHERE PHG NOT IN(4,5)
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
LIKE
– Lấy tất cả chuỗi giống vớimẫu
SELECT MANV,TENNV
FROM NHANVIEN
WHERE HONV LIKE ‘Nguyen_ _ _ _’
1 Ký tự bất kỳ
SELECT MANV,TENNV
FROM NHANVIEN
WHERE HONV LIKE ‘Nguyen%’
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
Nhiều ký tự bấtkỳ
NOT LIKE
– Lấy tất cả những chuỗi khônggiống
NULL
– Sử dụng trong trường hợp:
• Không biết (value unknown)
• Không thể áp dụng (value inapplicable)
• Không tồn tại (value un witheld)
SELECT MANV, TENNV FROM NHANVIEN
WHERE MA_NQL ISNULL
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
SELECT MANV, TENNV FROM NHANVIEN
WHERE MA_NQL IS NOTNULL
Không sử dụng WHERE
– Kết quả là phép tích “ ề-các”
lOMoARcPSD|36991220
Mệnh ề WHERE (tt)
lOMoARcPSD|36991220
Mệnh ề FROM
Tên, Bí danh
SELECT TENPHG, DIADIEM SELECT TENPHG,DIADIEM FROM PHONGBAN, DDIEM_PHG FROM
PHONGBAN, DDIEM_PHG
WHERE MAPHG=MAPHG
WHERE PhongBan.MAPHG=DDiem_PHG.MAPHG
Bí danh
lOMoARcPSD|36991220
Mệnh ề ORDER BY
Dùng ể hiển thị kết quả truy vấn theo thứ tự
Cú pháp:
SELECT <danh sáchcột>
FROM <danh sách các bảng>
WHERE < iềukiện>
ORDER BY <danh sách cột sắpxếp>
– ASC: Sắp xếp tăng (mặc ịnh) – DESC:
Sắp xếp giảm
Mệnh ề ORDER BY (tt)
Ví dụ:
SELECT MANV, MADA
FROM PHANCONG
lOMoARcPSD|36991220
ORDER BY MANV DESC, MADA
MANV MADA
999888777 10
999888777 30
888777666 10
888777666 30
333222111 10
333222111 20
333222111 30
Gom nhóm - GROUP
BY
Cú pháp:
SELECT <danh sáchcột>
FROM <danh sách các bảng>
WHERE < iềukiện>
GROUP BY <danh sách cột gom nhóm>
lOMoARcPSD|36991220
Mỗi bộ trong nhóm sẽ có cùng giá trị tại các thuộc tính gom
nhóm. Một số hàm sử dụng cho GROUP BY:
- AVG(): Tính bình quân
- SUM(): Tính tổng
- COUNT(): Đếm Chú ý:
– Mỗi thuộc tính liệt kê sau SELECT sẽ có 1 giá trị ứng với mỗi nhóm.
– Tất cả các thuộc tính sau SELECT phải xuất hiện ở sau mệnh ề
GROUP BY (Trừ thuộc tính có giá trị làhàm)
– Có thể có thuộc tính xuất hiện ở GROUP BY nhưng không xuất hiện
ở SELECT
Mệnh ề GROUP BY (tt)
Ví dụ: Cho biết số lượng nhân viên ở mỗi phòng ban
SELECT PHG, COUNT(*) AS ‘So NV’
FROM NHANVIEN
lOMoARcPSD|36991220
GROUP BY PHG
Hoặc
SELECT T ENPHG, COUNT(*) AS ‘So NV’
FROM NHANVIEN, PHONGBAN WHERE
PHG=MAPHG
GROUP BY TENPHG
Mệnh ề GROUP BY (tt)
Ví dụ: Hãy cho biết mã nhân viên, số lượng ề án và tổng thời
gian mà họ tham gia
SELECT MaNV, COUNT(*) AS ‘So DA’,
SUM(THOIGIAN) AS ‘TongTG’
FROM PHANCONG
GROUP BY MaNV
SELECT NV.MaNV,TENV, COUNT(*) AS ‘SoDA’,
SUM(THOIGIAN) AS ‘TongTG’
lOMoARcPSD|36991220
FROM PHANCONG PC, NHANVIENNV WHERE
PC.MaNV = NV.MaNV GROUP BY
PC.MaNV,TenNV
MaNV So DA Tong TG
MaNV So DA Tong TG
999888777
999888777
10
30
32.5
10
888777666
888777666
10
30
10
10
333222111
10
35.7
333222111
20
25
333222111
30
5
999888777
2
42.5
888777666
2
20
333222111
3
65.7
lOMoARcPSD|36991220
Mệnh ề HAVING
Ví dụ: Hãy tìm những nhân viên tham gia từ 2 ề án trở
lên.
lOMoARcPSD|36991220
Mệnh ề HAVING (tt)
Được sử dụng khi cần lọc ra những nhóm thỏa mãn iều
kiện nào ó Cú pháp:
Ví dụ:
– Tìm mã những nhân viên
tham gia nhiều hơn 2 Đề án
SELECT MaNV, COUNT(*) AS ‘SoDA’, FROM PHANCONG
GROUP BY MaNV
HAVING COUNT (*) > 2
SELECT <danh sáchcột>
FROM <danh sách các bảng>
WHERE < iềukiện>
GROUP BY <danh sách cột gom nhóm>
HAVING < iều kiện chọn nhóm>
lOMoARcPSD|36991220
Mệnh ề HAVING (tt)
Cho biết những phòng ban có lương trung bình của nhân
viên lớn hơn 3 triệu
SELECT PHG, AVG(LUONG) AS ‘Luong TB’
FROM NHANVIEN
GROUP BY PHG
HAVING AVG(LUONG) > 3000000
Hoặc
SELECT NV.PHG, PB.TenPB, Avg(NV.Luong) AS "TBLuong"
FROM NhanVien AS NV, PhongBan ASPB
WHERE NV.Phg = PB.MaPB
GROUP BY NV.PHG, PB.TenPB
lOMoARcPSD|36991220
HAVING AVG(LUONG) > 3000000;
X. Các hàm thao tác dữ liệu (tt)
Các hàm tính toán trên bản ghi
– ABS: tính trị tuyệt ối
– POWER(x, y): trả về xy
– SQRT: Tính căn bậc 2
– LOG: Tính Log tự nhiên
– EXP: Tính ex
– SIGN (x): Kiểm tra dấu của x (trả về -1 | 0 | 1)
– ROUND (x, n): làm tròn x tới n số lẻ (Access là RND)
– Các hàm lượng giác: SIN, COS, TAN, ASIN,ACOS,…
lOMoARcPSD|36991220
3.3. Các hàm thao tác dữ liệu (tt)
Các hàm xử lý chuỗi
– LEN (str) : Cho chiều dài chuỗi ký tự
– LEFT (str, n): Lấy n ký tự phía trái của chuỗi str
– RIGHT (str, n): Lấy n ký tự phía phải của chuỗi str
– MID (str, p, n): Lấy n ký tự của chuỗi str kể từ vị trí p trong
dãy
3.3. Các hàm thao tác dữ liệu (tt)
Các hàm xử lý thời gian
– DATE(): Cho ngày tháng năm hiện tại
– DAY (dd): Cho số thứ tự ngày trong tháng của biểu thức
ngày dd
lOMoARcPSD|36991220
– MONTH (dd) Cho số thứ tự tháng trong năm của biểu thức
ngày dd
– YEAR (dd) Cho năm của biểu thức ngày dd
– HOUR (tt) Cho giờ trong ngày (0- 23)
– MINUTE (tt) Cho số phút của thời gian tt
– SECONDS (tt) Cho số giây của biểu thức giờ tt.
3.4. Truy vấn từ nhiều bảng
Truy vấn trên nhiều bảng về bản chất giống như truy vấn
trên 1 bảng (ã kết nhiều bảng lại).
Sử dụng các phép kết (join)
– Kết bằng (phép kết nội – inner join)
– Kết ngoài (outter join)
lOMoARcPSD|36991220
– Truy vấn lồng nhau
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
Phép kết tự nhiên
SELECT <danh sách cột>
FROM <danh sách các bảng> WHERE < iều kiện kếtnối>
Hoặc
SELECT <danh sáchcột>
FROM <bảng 1> INNER JOIN <bảng 2> ON < iều kiện kết nối> WHERE < iều
kiện chọndòng>
Ví dụ: Đưa ra danh sách nhân viên và tên phòng làm việc
SELECT HoNV, Tenlot, TenNV, TenPB
FROM Nhanvien, PhongBan
WHERE PHG = MaPB
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
Ví dụ: Tìm mã và họ tên các nhân viên làm việc ở phòng
‘Kinh doanh’
SELECT MaNV, HoNV +’ ‘ + Tenlot + ‘ ‘ + TenNV AS ‘Ho ten’,TenPB
FROM Nhanvien INNER JOIN PhongBan ON PHG = MaPB
WHERE TenPB LIKE ‘%Kinh doanh’
Hoặc:
SELECT MaNV, HoNV +’ ‘ + Tenlot + ‘ ‘ + TenNV AS ‘Ho ten’,TenPB
FROM Nhanvien, PhongBan
WHERE PHG = MaPB AND TenPB LIKE ‘%Kinh doanh’
Phép kết ngoài
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
SELECT <danh sách cột>
FROM <bảng 1> LEFT | RIGHT | [OUTTER] JOIN <bảng 2>
ON < iều kiện kết>
WHERE < iều kiện chọn dòng>
Ví dụ: Cho biết nhân viên và tên của phòng ban mà họ là
trưởng phòng (nếu có)
SELECT HoNV, Tenlot, TenNV, TenPB
FROM NhanVien NV LEFT JOIN PhongBan PB
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
ON NV.MaNV=PhongBan.TrgPhg
Ví dụ: Tìm họ tên nhân viên và tên thân nhân của họ
(nếu có)
SELECT NV.MaNV, HoNV, TenLot, TenNV, TenTN, Quanhe
FROM ThanNhan TN RIGHT JOIN NhanVien NV
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
ON TN.MaNV = NV.MaNV
Truy vấn lồng nhau
– Một câu truy vấn (SELECT) lồng vào câu truy vấn khác gọi là
truy vấn lồng nhau hay Subquery
lOMoARcPSD|36991220
3.4. Truy vấn từ nhiều bảng (tt)
– Subquery ược bao bởi cặp dấu ngoặc (.......) và có thể lồng
nhau nhiều mức.
– Subquery ược lồng vào sau từ khóa WHERE hoặc HAVING
– Câu truy vấn con thường trả về 1 tập các giá trị
– Nếu có nhiều truy vấn con sau WHERE thì thường ược kết
hợp với nhau bởi các phép toán logic
lOMoARcPSD|36991220
Truy vấn lồng nhau (tt)
SELECT <danh sách cột>
FROM <danh sách bảng>
Truy vấn cha WHERE <so sánh tập hợp> (
SELECT <danh sách cột>
FROM <danh sách bảng>
WHERE < iều kiện>
)
Truy vấn con
Các phép toán tập hợp
– IN
lOMoARcPSD|36991220
Truy vấn lồng nhau (tt)
– NOT IN
– ALL
– ANY / SOME
– EXISTS
– NOT EXISTS
Có 2 loại truy vấn lồng
– Lồng phân cấp:
• Mệnh ề WHERE của Subquery không tham chiếu ến các thuộc
tính của các bảng trong mệnh ề FROM của truy vấn cha
• Khi thực hiện truy vấn con ược thực hiện trước
lOMoARcPSD|36991220
Truy vấn lồng nhau (tt)
– Lồng tương quan:
• Mệnh ề WHERE của Subquery tham chiếu ến ít nhất 1 thuộc tính
của bảng trong mệnh ề FROM của truy vấn cha
• Khi thực hiện, câu truy vấn con ược thực nhiều lần – mỗi lần ứng
với 1 bộ của câu truy vấn cha
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
Ví dụ - Truy vấn lồng phân cấp
Đưa ra mã nhân viên, họ tên nhân viên thuộc phòng ‘Tài
chính’ hoặc phòng ‘Kế hoạch’
SELECT MaNV, HoNV, TenLot, TenNV
FROM NHANVIEN
WHERE Phg IN (
SELECT MaPHG
FROM PHONGBAN
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
WHERE TenPB LIKE ‘%Tài chính’ OR TenPB LIKE ‘%Kế hoạch’
)
Hãy tìm những ề án có nhân viên họ ‘Nguyễn’ tham gia
SELECT MaDA, TenDA
FROM DEAN
WHERE MaDA IN (
SELECT MaDA
FROM PHANCONG PC, NHANVIEN NV
WHERE PC.MaNV = NV.MaNV AND
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
NV.HoNV LIKE ‘%Nguyễn%’
)
Hãy tìm những nhân viên không có thân nhân nào
SELECT *
FROM NHANVIEN
WHERE MaNV NOT IN (
SELECT MaNV
FROM THANNHAN
)
SELECT *
FROM NHANVIEN
WHERE MaNV <> ALL(
SELECT MaNV
FROM THANNHAN
)
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
Tìm những nhân viên có lương lớn hơn lương của ít nhất
một nhân viên phòng 4
SELECT *
FROM NHANVIEN
WHERE LUONG > ANY (
SELECT LUONG FROM NHANVIEN WHERE PhG = 4
)
Tìm những trưởng phòng có tối thiểu 1 thân nhân
SELECT *
FROM NHANVIEN
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
WHERE MANV IN (SELECT MaNV FROM THANNHAN) AND MANV IN
(SELECT TRPHG FROM PHONGBAN)
Tìm những Nhân viên có lương cao hơn lương của mọi
nhân viên phòng 4
lOMoARcPSD|36991220
Truy vấn lồng tương quan
– Mệnh ề WHERE của Subquery tham chiếu ến ít nhất 1 thuộc tính
của bảng trong mệnh ề FROM của truy vấn cha
– Khi thực hiện, câu truy vấn con ược thực nhiều lần – mỗi lần ứng
với 1 bộ của câu truy vấn cha Ví dụ:
– Tìm những trưởng phòng có ít nhất 1 thân nhân
SELECT *
FROM NHANVIEN NV
WHERE EXISTS ( SELECT * FROM THANNHAN TN
WHERE TN.MaNV = NV.MaNV)AND
EXISTS (SELECT TrgPHG FROM PHONGBAN
WHERE TrgPHG = NV.MaNV)
lOMoARcPSD|36991220
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên không có thân nhân nào
SELECT *
FROM NHANVIEN NV
WHERE NOT EXISTS (
SELECT *
FROM THANNHAN TN
WHERE TN.MaNV = NV.MaNV
)
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên có lương cao hơn lương trung bình
của phòng
lOMoARcPSD|36991220
SELECT *
FROM NHANVIEN NV1
WHERE NV1.Luong > (
SELECT AVG(NV2.Luong)
FROM NHANVIEN NV2
WHERE NV2.Phg = NV1.Phg
)
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên có lương bằng lương cao hơn lương
của ít nhất 1 nhân viên thuộc phòng ‘Tài chính’
SELECT *
FROM NHANVIEN NV1
lOMoARcPSD|36991220
WHERE EXISTS (
SELECT *
FROM NHANVIEN NV2, PHONGBAN PB
WHERE NV2.Phg = PB.MaPB AND
PB.TenPB LIKE ‘%Tài chính%’AND
NV1.Luong > NV2.Luong
)
Nhận xét IN và EXISTS
IN
– <tên cột> IN <câu truy vấn con>
– Thuộc tính ở mệnh ề SELECT câu truy vấn con phải có cùng
kiểu dữ liệu với thuộc tính ở mệnh ề WHERE trong câu truy
vấn cha
lOMoARcPSD|36991220
EXISTS
– Không cần có thuộc tính, hăng số hay biểu thức nàoứng trước
– Không cần liệt kê thuộc tính ở bệnh ề SELECT trong câu truy
vấn con
– Những câu truy vấn có = ANY hoặc IN ều có thể chuyển thành
câu truy vấn dùng EXISTS
Các phép toán tập hợp trong SQL
Phép hợp: UNION
lOMoARcPSD|36991220
Phép giao: INTERSECT Phép trừ: EXCEPT Cú pháp:
lOMoARcPSD|36991220
Phép toán tập hợp trong SQL (tt)
Ví dụ: Tìm các mã ề án có nhân viên họ ‘Nguyễn’ tham gia
hoặc trưởng phòng chủ trì ề án có họ ‘Nguyễn’
SELECT MaDA
FROM PHANCONG PC, NHANVIEN NV
WHERE (PC.MaNV = NV.MaNV) AND (HoNV = ‘Nguyễn’)
UNION (
SELECT MaDA
FROM NHANVIEN NV, PHONGBAN PB, DEAN DA
WHERE (NV.MaNV=PB.TrgPhg) AND (DA.Phong= PB.MaPB)
AND (NV.HoNV = ‘Nguyễn’)
)
lOMoARcPSD|36991220
3.5. Các lệnh cập nhật dữ liệu
Thêm 1 bộ (dòng) mới
Sửa 1 bộ (dòng)
Xóa 1 bộ (dòng)
Tạo mới 1 quan hệ (bảng) với nội dung từ CSDL
lOMoARcPSD|36991220
3.5.1. Thêm dòng vào bảng
Cú pháp
INSERT INTO <tên bảng> ( <danh sách các thuộc tính>) VALUES
(<danh sách các giá trị>)
Ví dụ:
lOMoARcPSD|36991220
3.5.1. Thêm dòng vào bảng (tt)
Nhận xét lệnh INSERT INTO
– Thứ tự giá trị sau VALUES phải trùng với thứ tự cộtsau INSERT
INTO
– Có thể thêm giá trị NULL ở thuộc tính không là khóa chính
lOMoARcPSD|36991220
– Lệnh INSERT INTO có thể không thực hiện ược (lỗi) nếu vi
phạm ràng buộc toàn vẹn (RBTV)
• Khóa chính
• Tham chiếu
• Thuộc tính NOT NULL
3.5.1. Thêm dòng vào bảng (tt) Thêm nhiều
dòng
INSERT INTO <tên bảng> ( <danh sách các thuộc tính>)
<câu truy vấn con>
Ví dụ:
lOMoARcPSD|36991220
3.5.2. Sửa dòng trong bảng
Cú pháp
UPDATE <tên bảng>
SET <tên thuộc tính> = <giá trị mới>,
<tên thuộc tính> = <giá trị mới>,
……
[ WHERE < iều kiện> ]
lOMoARcPSD|36991220
Ví dụ: Tăng lương 30% cho những người là ‘nữ’
UPDATE NHANVIEN
SET Luong = Luong*1.3,
WHERE phai = ‘Nữ’
3.5.2. Sửa dòng trong bảng (tt)
Nhận xét
– Lệnh UPDATE cập nhật những dòng thỏa iều kiệnsau WHERE
– Nếu không chỉ ịnh iều kiện sau WHERE thì mọi dòngtrong
bảng sẽ ược cập nhật giá trị mới
– Lệnh UPDATE có thể gây ra vi phạm RBTV
lOMoARcPSD|36991220
• Không cho chỉnh sửa
• Sửa luôn dòng có giá trị tham chiếu ến (CASCADE)
3.5.3. Xóa dòng trong bảng
Cú pháp
DELETE FROM <tên bảng> WHERE <
iều kiện>
Ví dụ:
lOMoARcPSD|36991220
lOMoARcPSD|36991220
3.5.3. Xóa dòng trong bảng (tt)
Ví dụ: Xóa i những nhân viên làm ở phòng ‘Dự án’
DELETE FROM NHANVIEN
WHERE PHG IN (
SELECT MaPB
FROM PHONGBAN
WHERE TenPB = ‘Phòng Dự án’ )
Nhận xét
lOMoARcPSD|36991220
3.5.3. Xóa dòng trong bảng (tt)
– Số dòng bị xóa phụ thuộc vào iều kiện sau WHERE
– Nếu không chỉ rõ iều kiện sau WHERE tất cả các dòng trong
bảng sẽ bị xóa
– Lệnh DELETE FROM có thể gây ra vi phạm RBTV
• Không cho xóa
• Xóa luôn những dòng có giá trị ang tham chiếu ến
• Đặt Null cho những giá trị tham chiếu
lOMoARcPSD|36991220
3.5.3. Xóa dòng trong bảng (tt)
lOMoARcPSD|36991220
3.5.4. Tạo bảng từ CSDL Cú pháp:
SELECT <danh sách cột/biểu thức>
FROM <tên bảng nguồn>
INTO <tên bảng ích>
WHERE <Điều kiện>
GROUP BY <cột gom nhóm>
HAVING < iều kiện chọn nhóm>
ORDER BY <cột sắp xếp>
3.5.4. Tạo bảng từ CSDL (tt)
Ví dụ
lOMoARcPSD|36991220
SELECT PB.TenPB, AVG(NV.Luong) ASTBLuong
FROM NHANVIEN NV, PHONGBAN PB
INTO TABLE TBLUONG_PHONGBAN
WHERE NV.PHG = PB.MaPB
GROUP BY PHG, TenPB
3.6. Các lệnh liên quan cấu trúc CSDL
Các kiểu dữ liệu trong SQL
Tên kiểu
Ý nghĩa
Char (w)
Kiểu ký tự ộ rộng cố ịnh (w)
Varchar(w)
Kiểu ký tự có ộ rộng thay ổi 0 w
Integer
Kiểu số nguyên
lOMoARcPSD|36991220
Byte
Kiểu số nguyên nhỏ (0 255)
Numberic (w,s)
Số thực rộng w vị trí (cả dấu chấm) có s chữ số thập phân
Real, Double
Số thực dấu phẩy ộng
Float (n)
Số thực dấu phẩy ộng có ít nhất n chữ số
Date
Kiểu ngày tháng
Time
Kiểu thời gian (giờ/phút/giây)
Logical
Kiểu logic (True/False)
3.6.1. Lệnh tạo bảng CREATE TABLE
Để ịnh nghĩa 1 bảng cần chỉ ra:
– Tên bảng
– Các thuộc tính
• Tên thuộc tính
lOMoARcPSD|36991220
• Kiểu dữ liệu
• RBTV trên thuộc tính
Cú pháp
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
Ví dụ:
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
RBTV
– NOT NULL
– NULL
– UNIQUE– DEFAUL
– PRIMARY KEY
– FOREIGN KEY / REFERENCES
– CHECK
Đặt tên cho RBTV
Ví dụ RBTV
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
Ví dụ:
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
Ví dụ - ặt tên cho RBTV
lOMoARcPSD|36991220
3.6.1. CREATE TABLE (tt)
Ví dụ - ặt tên cho RBTV
lOMoARcPSD|36991220
3.6.2. Lệnh sửa bảng (tt)
3.6.2. Lệnh sửa bảng
Thay ổi cấu trúc bảng
– Thêm cột mới
– Xóa cột
lOMoARcPSD|36991220
3.6.2. Lệnh sửa bảng (tt)
– Mở rộng cột
Thay ổi RBTV
– Thêm RBTV
– Xóa RBTV
Ví dụ - thay ổi cấu trúc
lOMoARcPSD|36991220
3.6.2. Lệnh sửa bảng (tt)
Ví dụ - thay ổi RBTV
lOMoARcPSD|36991220
3.6.2. Lệnh sửa bảng (tt)
lOMoARcPSD|36991220
3.6.3. Xóa bảng Cú
pháp:
Ví dụ:
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
87
3.6.3. Xóa bảng (tt)
3.8. Bài tập
1. Tìm những nhân viên làm việc ở phòng số 4
2. Tìm những nhân viên có mức lương trên 30000
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
88
3. Tìm các nhân viên có mức lương trên 25,000 ở phòng 4 hoặc các nhân
viên có mức lương trên 30,000 ở phòng 5
4. Cho biết họ tên đầy đủ của các nhân viên có họ bắt đầu bằng ký tự ‘N’
5. Cho biết các nhân viên sinh trước năm 1975
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ, DIA_DIEM)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
1. Tìm những nhân viên làm việc ở phòng số 4
SELECT * FROM NHANVIEN WHERE PHG = 4
2. Tìm những nhân viên có mức lương trên 30000
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
89
SELECT * FROM NHANVIEN WHERE LUONG > 30000
3. Tìm các nhân viên có mức lương trên 25,000 ở phòng 4 hoặc các nhân viên có
mức lương trên 30,000 ở phòng 5
SELECT * FROM NHANVIEN WHERE (LUONG > 25000 AND PHG = 4) OR (LUONG >
30000 AND PHG = 5)
4. Cho biết họ tên đầy đủ của các nhân viên có họ bắt đầu bằng ký tự ‘N’
SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE HONV LIKE N'N%'
5. Cho biết các nhân viên sinh trước năm 1975
SELECT * FROM NHANVIEN WHERE YEAR(NGSINH) < 1975
6. Cho biết các nhân viên sinh trước ngày 30/4/1975 hoặc ở TP HCM hoặc làm
việc tại phòng số 4
7. Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng
8. Tìm tên những người trưởng phòng của từng phòng ban
9. Tìm tên và địa chỉ của tất cả các nhân viên của phòng "Nghiên cứu".
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
90
10. Với mọi đề án ở "Ha Noi", liệt kê các mã số đề án (MADA), mã số phòng ban
chủ trì đề án (PHONG), họ tên trưởng phòng (HONV, TENLOT, TENNV) cũng như
địa chỉ (DCHI) và ngày sinh (NGSINH) của người ấy.
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
6. Cho biết các nhân viên sinh trước ngày 30/4/1975 hoặc ở TP HCM hoặc làm
việc tại phòng số 4
SELECT * FROM NHANVIEN WHERE (YEAR(NGSINH) < 1975) OR (DCHI LIKE N'%Tp
hcm') OR (PHG = 4)
7. Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng
SELECT P.TENPHG, D.DIADIEM FROM PHONGBAN P, DIADIEM_PHG D WHERE
P.MAPHG = D.MAPHG
8. Tìm tên những người trưởng phòng của từng phòng ban
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
91
SELECT TENNV FROM NHANVIEN N, PHONGBAN P WHERE N.MANV = P.TRPHG
9. Tìm tên và địa chỉ của tất cả các nhân viên của phòng "Nghiên cứu".
SELECT TENNV, DCHI FROM NHANVIEN N, PHONGBAN P WHERE N.PHG =
P.MAPHG AND P.TENPHG LIKE N'Nghiên cứu'
10. Với mọi đề án ở "Ha Noi", liệt kê các mã số đề án (MADA), mã số phòng
ban chủ trì đề án (PHONG), họ tên trưởng phòng (HONV, TENLOT, TENNV)
cũng như địa chỉ (DCHI) và ngày sinh (NGSINH) của người ấy.
SELECT D.MADA, D.PHONG, N.HONV, N.TENLOT, N.TENNV, N.DCHI, N.NGSINH
FROM NHANVIEN N, PHONGBAN P, DEAN D WHERE N.MANV = P.TRPHG AND
P.MAPHG = D.PHONG AND DDIEM_DA LIKE N'Hà Nội'
11. Cho biết tên các đề án mà nhân viên Đinh Bá Tiến đã tham gia
12. Cho biết số lượng đề án của công ty
13. Cho biết số lượng đề án do phòng 'Nghiên Cứu' chủ trì
14. Cho biết lương trung bình của các nữ nhân viên
15. Với mỗi nhân viên, cho biết số lượng nhân viên mà nhân viên đó quản lý trực tiếp.
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
92
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
11. Cho biết tên các đề án mà nhân viên Đinh Bá Tiến đã tham gia
SELECT D.TENDA FROM NHANVIEN N, PHANCONG P, DEAN D WHERE N.MANV =
P.MA_NVIEN AND P.MADA = D.MADA AND (HONV + ' ' + TENLOT + ' ' + TENNV) =
N'Đinh Bá Tiên'
12. Cho biết số lượng đề án của công ty
SELECT COUNT(MADA) AS SODEAN FROM DEAN
13. Cho biết số lượng đề án do phòng 'Nghiên Cứu' chủ trì
SELECT COUNT(D.MADA) AS SODEAN FROM DEAN D, PHONGBAN P WHERE D.PHONG
= P.MAPHG AND P.TENPHG LIKE N'Nghiên cứu'
14. Cho biết lương trung bình của các nữ nhân viên
lOMoARcPSD|36991220
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
93
SELECT AVG(LUONG) AS LUONGTB FROM NHANVIEN WHERE PHAI LIKE N'Nữ'
15. Với mỗi nhân viên, cho biết số lượng nhân viên mà nhân viên đó quản lý trực tiếp.
SELECT Q.TENNV, COUNT(N.MANV) AS SONV FROM NHANVIEN N, NHANVIEN Q
WHERE N.MA_NQL = Q.MANV GROUP BY Q.TENNV
lOMoARcPSD|36991220
16. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của
những nhân viên làm việc cho phòng ban đó.
17. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó
chủ trì
18. Với mỗi phòng ban, cho biết tên phòng ban, họ tên người trưởng phòng và số
lượng đề án mà phòng ban đó chủ trì
19. Với mỗi phòng ban có mức lương trung bình lớn hơn 40,000, cho biết tên phòng
ban và số lượng đề án mà phòng ban đó chủ trì.
20. Cho biết số đề án diễn ra tại từng địa điểm
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
16. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của
những nhân viên làm việc cho phòng ban đó.
lOMoARcPSD|36991220
SELECT P.TENPHG, AVG(N.LUONG) AS LUONGTB FROM NHANVIEN N, PHONGBAN P
WHERE N.PHG = P.MAPHG GROUP BY P.TENPHG
17. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó
chủ trì
SELECT P.TENPHG, COUNT(D.MADA) AS SODEAN FROM PHONGBAN P, DEAN D
WHERE P.MAPHG = D.PHONG GROUP BY P.TENPHG
18. Với mỗi phòng ban, cho biết tên phòng ban, họ tên người trưởng phòng và số
lượng đề án mà phòng ban đó chủ trì
SELECT P.TENPHG, N.HONV, N.TENLOT, N.TENNV, COUNT(D.MADA) AS SODEAN
FROM PHONGBAN P, DEAN D, NHANVIEN N WHERE P.MAPHG = D.PHONG AND
P.TRPHG = N.MANV GROUP BY P.TENPHG, N.HONV, N.TENLOT, N.TENNV
19. Với mỗi phòng ban có mức lương trung bình lớn hơn 40,000, cho biết tên phòng
ban và số lượng đề án mà phòng ban đó chủ trì.
SELECT P.TENPHG, COUNT(D.MADA) AS SODEAN FROM NHANVIEN N, PHONGBAN P,
DEAN D WHERE P.MAPHG = N.PHG AND P.MAPHG = D.PHONG GROUP BY P.TENPHG
HAVING AVG(N.LUONG) > 40000
20. Cho biết số đề án diễn ra tại từng địa điểm
SELECT DDIEM_DA, COUNT(DDIEM_DA) AS SODEAN FROM DEAN GROUP BY
DDIEM_DA
21. Cho biết danh sách các đề án (MADA) có: nhân công với họ (HONV) là ‘Dinh’ hoặc ,
có người trưởng phòng chủ trì đề án với họ (HONV) là ‘Dinh’.
lOMoARcPSD|36991220
22. Danh sách những nhân viên (HONV, TENLOT, TENNV) có trên 2 thân nhân.
23. Danh sách những nhân viên (HONV, TENLOT, TENNV) không có thân nhân nào.
24. Danh sách những trưởng phòng (HONV, TENLOT, TENNV) có tối thiểu một thân
nhân.
25. Tìm họ (HONV) của những trưởng phòng chưa có gia đình.
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
21. Cho biết danh sách các đề án (MADA) có: nhân công với họ (HONV) là ‘Dinh’ hoặc ,
có người trưởng phòng chủ trì đề án với họ (HONV) là ‘Dinh’.
SELECT TENDA FROM DEAN WHERE MADA IN (SELECT D.MADA FROM DEAN D,
PHANCONG P, NHANVIEN N WHERE D.MADA = P.MADA AND P.MA_NVIEN = N.MANV
AND N.HONV = N'Đinh’) OR MADA IN (SELECT D.MADA FROM DEAN D, PHONGBAN B,
NHANVIEN N WHERE D.PHONG = B.MAPHG AND B.TRPHG = N.MANV AND N.HONV =
N'Đinh')
22. Danh sách những nhân viên (HONV, TENLOT, TENNV) có trên 2 thân nhân.
lOMoARcPSD|36991220
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, THANNHAN T WHERE
N.MANV = T.MA_NVIEN GROUP BY N.HONV, N.TENLOT, N.TENNV HAVING
COUNT(T.MA_NVIEN) > 2
23. Danh sách những nhân viên (HONV, TENLOT, TENNV) không có thân nhân nào.
SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE MANV NOT IN (SELECT
N.MANV FROM NHANVIEN N, THANNHAN T WHERE N.MANV = T.MA_NVIEN GROUP
BY N.MANV)
24. Danh sách những trưởng phòng (HONV, TENLOT, TENNV) có tối thiểu một thân
nhân.
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHONGBAN P, THANNHAN
T WHERE P.TRPHG = N.MANV AND N.MANV = T.MA_NVIEN GROUP BY N.HONV,
N.TENLOT, N.TENNV
25. Tìm họ (HONV) của những trưởng phòng chưa có gia đình.
SELECT N.HONV FROM NHANVIEN N, PHONGBAN P WHERE P.TRPHG = N.MANV AND
N.MANV NOT IN (SELECT N.MANV FROM NHANVIEN N, THANNHAN T WHERE N.MANV
= T.MA_NVIEN GROUP BY N.MANV)
26. Danh sách những nhân viên (HONV, TENLOT, TENNV) làm việc trong mọi đề án của
công ty
27. Danh sách những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án
do phòng số 4 chủ trì.
lOMoARcPSD|36991220
28. Tìm những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án mà
nhân viên Đinh Bá Tiến làm việc
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ)
DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
26. Danh sách những nhân viên (HONV, TENLOT, TENNV) làm việc trong mọi đề án của
công ty
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHANCONG P WHERE
N.MANV = P.MA_NVIEN GROUP BY N.HONV, N.TENLOT, N.TENNV HAVING
COUNT(DISTINCT P.MADA) = (SELECT COUNT(D.MADA) FROM DEAN D)
27. Danh sách những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án
do phòng số 4 chủ trì.
lOMoARcPSD|36991220
SELECT N.HONV, N.TENLOT, N.TENNV FROM DEAN D, CONGVIEC C, PHANCONG P,
NHANVIEN N WHERE D.MADA = C.MADA AND C.MADA = P.MADA AND C.STT = P.STT
AND P.MA_NVIEN = N.MANV AND D.PHONG = 4 GROUP BY N.HONV, N.TENLOT,
N.TENNV HAVING COUNT(DISTINCT P.MADA) = (SELECT COUNT(MADA) FROM DEAN
WHERE PHONG = 4)
28. Tìm những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án mà
nhân viên Đinh Bá Tiến làm việc
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHANCONG P WHERE
N.MANV = P.MA_NVIEN GROUP BY N.MANV, N.HONV, N.TENLOT, N.TENNV HAVING
COUNT(DISTINCT P.MADA) = (SELECT COUNT(DISTINCT PC.MADA) FROM NHANVIEN
NV, PHANCONG PC WHERE NV.MANV = PC.MA_NVIEN AND (NV.HONV + ' ' +
NV.TENLOT + ' ' + NV.TENNV) = N'Đinh Bá Tiên')
| 1/99
| | Tải xuống

Có thể bạn quan tâm:

Preview text:

lOMoARcPSD| 36991220 Ngôn ngữ truy vấn SQL lOMoARcPSD| 36991220 Nội dung
1. Khái quát về ngôn ngữ truy vấn dữ liệu 2. Câu lệnh SELECT
3. Các hàm thao tác dữ liệu
4. Truy vấn thông tin từ nhiều bảng
5. Các lệnh cập nhật dữ liệu
6. Các lệnh liên quan tới cấu trúc
7. Các lệnh giao quyền truy cập CSDL 8. Bài tập 3.1. Giới thiệu SQL
SQL ược xem là yếu tố chính óng góp vào sự thành công của CSDL
quan hệ khi áp dụng trong thực tế. lOMoARcPSD| 36991220
Là ngôn ngữ mức cao, người dùng chỉ cần xác ịnh kết quả của truy
vấn là gì, phần còn lại là tính toán và tối ưu hoá câu lệnh ược DBMS ảm nhiệm.
1970: SQL (Structured Query Language) bắt nguồn từ ngôn ngữ
SEQUEL (Structured English QUEry Language), ngôn ngữ ược
thiết kế tại tập oàn IBM nhằm khi ưa ra hệ quản trị CSDL có tên là SYSTEM-R 3.1. Giới thiệu SQL (tt)
Năm 1976: SEQUEL ược cải tiến thành SEQUEL2
Năm 1978-1979: SEQUEL2 ược nâng cấp và ổi tên thành
SQL (nhưng vẫn ọc là SEQUEL) lOMoARcPSD| 36991220
Chuẩn SQL ầu tiên có tên gọi SQL-86 (SQL1) ược công nhận
và chuẩn hóa bởi ANSI và ISO.
Năm 1992: SQL1 ược mở rộng với nhiều tính năng mới và
ược gọi là SQL-92 (SQL2).
Năm 1999: Chuẩn SQL-99 (SQL3) ra ời
Phiên bản mới nhất hiện nay là SQL-2011 3.1. Giới thiệu SQL (tt)
Ngôn ngữ giao thiết CSLD gồm:
– DDL – Data Definition Language
– DML – Data Manipulation Language
– SQL – Structured Query Language lOMoARcPSD| 36991220
– DCL – Data Control Language SQL gồm 2 nhóm lệnh
– DDL: Tạo cấu trúc CSDL
– DML: Thao tác trên CDSDL • CREATE • SELECT • INSERT, UPDATE, DELETE…
Cho các lược ồ quan hệ
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI,
LUONG, DIACHI, NGAYSINH, MA_NQL, PHG) lOMoARcPSD| 36991220
PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
3.2. lệnh SELECT – Truy vấn dữ liệu Gồm 3 mệnh ề cơ bản SELECT FROM WHERE < iều kiện> lOMoARcPSD| 36991220 –
• Tên các cột cần hiển thị ở kết quả truy vấn –
• Tên các bảng (nguồn) dữ liệu khi truy vấn – < iều kiện>
• Xác ịnh biểu thức boolean xác ịnh dòng (bộ) nào ược trích ra
• Nối các biểu thức: AND, OR, NOT
• Phép toán: >, <, <=, >=, =, <>, LIKE, BETWEEN 2. lệnh SELECT (tt) SQL và Đại số quan hệ lOMoARcPSD| 36991220 SELECT x FROM WHERE < iều kiện> SELECT L ( FROM R L C(R )) WHERE C lOMoARcPSD| 36991220 Ví dụ SELECT *
Lấy tất cả các cột của quan hệ FROM NHANVIEN WHERE PHG=5 PHG=5(NHANVIEN) lOMoARcPSD| 36991220 Mệnh ề SELECT lOMoARcPSD| 36991220 Mệnh ề SELECT (tt) Tên, Bí danh
SELECT MANV, HONV AS 'Họ', TENLOT AS ‘Tên lót’, TENNV AS 'Tên' FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’ ( MANV,HO,TEN LOT,TEN MaNV,HONV,TENLOT,TENNV
PHG=5 PHAI=‘Nam’(NHANVIEN))) lOMoARcPSD| 36991220 Mệnh ề SELECT (tt) Mở rộng:
SELECT MANV, HONV +’ ‘+ TENLOT+’ ‘ TENNV AS ‘HO TEN’ FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’ lOMoARcPSD| 36991220 Mệnh ề SELECT (tt) Mở rộng:
SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nam’ lOMoARcPSD| 36991220 Mệnh ề SELECT (tt)
Loại bỏ các dòng trùng nhau SELECT LUONG FROM NHANVIEN SELECT DISTINCT LUONG
WHERE PHG=5 AND PHAI=‘Nam’ FROM NHANVIEN lOMoARcPSD| 36991220 Mệnh ề SELECT (tt)
WHERE PHG=5 AND PHAI=‘Nam’ lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) Ví dụ
Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’ SELECT MANV, TENNV lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG AND TENPGH=‘Nghiencuu’ Mệnh ề WHERE SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN lOMoARcPSD| 36991220 Mệnh ề WHERE (tt)
WHERE PHG=MAPHG AND TENPHG=‘Nghien cuu’ TRUE TRUE Độ ưu tiên
– Viết trước thực hiện trước
– Trong ngoặc thực hiện trước SELECT MANV, TENNV lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) FROM NHANVIEN, PHONGBAN
WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) ANDPHG=MAPHG lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) BETWEEN SELECT MANV, TENNV FROM NHANVIEN
WHERE LUONG>=2000000 ANDLUONG<=3000000 SELECT MANV, TENNV FROM NHANVIEN
WHERE LUONG BETWEEN 2000000 AND3000000 NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN lOMoARcPSD| 36991220 Mệnh ề WHERE (tt)
WHERE LUONG NOT BETWEEN 2000000 AND3000000 IN NOT IN SELECT MANV, TENNV WHERE PHG = 4 ORPHG=5 FROM NHANVIEN SELECT MANV, TENNV WHERE PHG IN (4,5) FROM NHANVIEN WHERE PHG NOT IN(4,5) SELECT MANV, TENNV FROM NHANVIEN lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) LIKE
– Lấy tất cả chuỗi giống vớimẫu SELECT MANV,TENNV FROM NHANVIEN
WHERE HONV LIKE ‘Nguyen_ _ _ _’ 1 Ký tự bất kỳ SELECT MANV,TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen%’ lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) Nhiều ký tự bấtkỳ NOT LIKE
– Lấy tất cả những chuỗi khônggiống NULL
– Sử dụng trong trường hợp:
• Không biết (value unknown)
• Không thể áp dụng (value inapplicable)
• Không tồn tại (value un witheld)
SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL ISNULL lOMoARcPSD| 36991220 Mệnh ề WHERE (tt)
SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOTNULL Không sử dụng WHERE
– Kết quả là phép tích “ ề-các” lOMoARcPSD| 36991220 Mệnh ề WHERE (tt) lOMoARcPSD| 36991220 Mệnh ề FROM Tên, Bí danh
SELECT TENPHG, DIADIEM SELECT TENPHG,DIADIEM FROM PHONGBAN, DDIEM_PHG FROM PHONGBAN, DDIEM_PHG WHERE MAPHG=MAPHG
WHERE PhongBan.MAPHG=DDiem_PHG.MAPHG Bí danh lOMoARcPSD| 36991220 Mệnh ề ORDER BY
Dùng ể hiển thị kết quả truy vấn theo thứ tự Cú pháp: SELECT FROM WHERE < iềukiện> ORDER BY
– ASC: Sắp xếp tăng (mặc ịnh) – DESC: Sắp xếp giảm Mệnh ề ORDER BY (tt) Ví dụ: SELECT MANV, MADA FROM PHANCONG lOMoARcPSD| 36991220 ORDER BY MANV DESC, MADA MANV MADA 999888777 10 999888777 30 888777666 10 888777666 30 333222111 10 333222111 20 333222111 30 Gom nhóm - GROUP SELECT BY FROM Cú pháp: WHERE < iềukiện> GROUP BY lOMoARcPSD| 36991220
Mỗi bộ trong nhóm sẽ có cùng giá trị tại các thuộc tính gom
nhóm. Một số hàm sử dụng cho GROUP BY: - AVG(): Tính bình quân - SUM(): Tính tổng - COUNT(): Đếm Chú ý:
– Mỗi thuộc tính liệt kê sau SELECT sẽ có 1 giá trị ứng với mỗi nhóm.
– Tất cả các thuộc tính sau SELECT phải xuất hiện ở sau mệnh ề
GROUP BY (Trừ thuộc tính có giá trị làhàm)
– Có thể có thuộc tính xuất hiện ở GROUP BY nhưng không xuất hiện ở SELECT Mệnh ề GROUP BY (tt)
Ví dụ: Cho biết số lượng nhân viên ở mỗi phòng ban
SELECT PHG, COUNT(*) AS ‘So NV’ FROM NHANVIEN lOMoARcPSD| 36991220 GROUP BY PHG Hoặc
SELECT T ENPHG, COUNT(*) AS ‘So NV’ FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG Mệnh ề GROUP BY (tt)
Ví dụ: Hãy cho biết mã nhân viên, số lượng ề án và tổng thời gian mà họ tham gia
SELECT MaNV, COUNT(*) AS ‘So DA’,
SELECT NV.MaNV,TENV, COUNT(*) AS ‘SoDA’, SUM(THOIGIAN) AS ‘TongTG’ SUM(THOIGIAN) AS ‘TongTG’ FROM PHANCONG GROUP BY MaNV lOMoARcPSD| 36991220
FROM PHANCONG PC, NHANVIENNV WHERE PC.MaNV = NV.MaNV GROUP BY 999888777 PC.MaNV,TenNV 2 42.5 888777666 2 20 333222111 3 65.7 999888777 10 32.5 MaNV So DA Tong TG 999888777 30 10 888777666 10 10 888777666 10 30 333222111 10 35.7 333222111 20 25 333222111 30 5 MaNV So DA Tong TG lOMoARcPSD| 36991220 Mệnh ề HAVING
Ví dụ: Hãy tìm những nhân viên tham gia từ 2 ề án trở lên. lOMoARcPSD| 36991220 Mệnh ề HAVING (tt)
Được sử dụng khi cần lọc ra những nhóm thỏa mãn iều kiện nào ó Cú pháp: SELECT FROM WHERE < iềukiện> GROUP BY Ví dụ:
HAVING < iều kiện chọn nhóm> – Tìm mã những nhân viên
tham gia nhiều hơn 2 Đề án
SELECT MaNV, COUNT(*) AS ‘SoDA’, FROM PHANCONG GROUP BY MaNV HAVING COUNT (*) > 2 lOMoARcPSD| 36991220 Mệnh ề HAVING (tt)
Cho biết những phòng ban có lương trung bình của nhân viên lớn hơn 3 triệu
SELECT PHG, AVG(LUONG) AS ‘Luong TB’ FROM NHANVIEN GROUP BY PHG
HAVING AVG(LUONG) > 3000000 Hoặc
SELECT NV.PHG, PB.TenPB, Avg(NV.Luong) AS "TBLuong"
FROM NhanVien AS NV, PhongBan ASPB WHERE NV.Phg = PB.MaPB GROUP BY NV.PHG, PB.TenPB lOMoARcPSD| 36991220
HAVING AVG(LUONG) > 3000000;
X. Các hàm thao tác dữ liệu (tt)
Các hàm tính toán trên bản ghi
– ABS: tính trị tuyệt ối
– POWER(x, y): trả về xy – SQRT: Tính căn bậc 2
– LOG: Tính Log tự nhiên – EXP: Tính ex
– SIGN (x): Kiểm tra dấu của x (trả về -1 | 0 | 1)
– ROUND (x, n): làm tròn x tới n số lẻ (Access là RND)
– Các hàm lượng giác: SIN, COS, TAN, ASIN,ACOS,… lOMoARcPSD| 36991220
3.3. Các hàm thao tác dữ liệu (tt) Các hàm xử lý chuỗi
– LEN (str) : Cho chiều dài chuỗi ký tự
– LEFT (str, n): Lấy n ký tự phía trái của chuỗi str
– RIGHT (str, n): Lấy n ký tự phía phải của chuỗi str
– MID (str, p, n): Lấy n ký tự của chuỗi str kể từ vị trí p trong dãy
3.3. Các hàm thao tác dữ liệu (tt)
Các hàm xử lý thời gian
– DATE(): Cho ngày tháng năm hiện tại
– DAY (dd): Cho số thứ tự ngày trong tháng của biểu thức ngày dd lOMoARcPSD| 36991220
– MONTH (dd) Cho số thứ tự tháng trong năm của biểu thức ngày dd
– YEAR (dd) Cho năm của biểu thức ngày dd
– HOUR (tt) Cho giờ trong ngày (0- 23)
– MINUTE (tt) Cho số phút của thời gian tt
– SECONDS (tt) Cho số giây của biểu thức giờ tt.
3.4. Truy vấn từ nhiều bảng
Truy vấn trên nhiều bảng về bản chất giống như truy vấn
trên 1 bảng (ã kết nhiều bảng lại).
Sử dụng các phép kết (join)
– Kết bằng (phép kết nội – inner join)
– Kết ngoài (outter join) lOMoARcPSD| 36991220 – Truy vấn lồng nhau lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt) Phép kết tự nhiên SELECT
FROM WHERE < iều kiện kếtnối> Hoặc SELECT
FROM INNER JOIN ON < iều kiện kết nối> WHERE < iều kiện chọndòng>
Ví dụ: Đưa ra danh sách nhân viên và tên phòng làm việc
SELECT HoNV, Tenlot, TenNV, TenPB FROM Nhanvien, PhongBan WHERE PHG = MaPB lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt)
Ví dụ: Tìm mã và họ tên các nhân viên làm việc ở phòng ‘Kinh doanh’
SELECT MaNV, HoNV +’ ‘ + Tenlot + ‘ ‘ + TenNV AS ‘Ho ten’,TenPB
FROM Nhanvien INNER JOIN PhongBan ON PHG = MaPB
WHERE TenPB LIKE ‘%Kinh doanh’ Hoặc:
SELECT MaNV, HoNV +’ ‘ + Tenlot + ‘ ‘ + TenNV AS ‘Ho ten’,TenPB FROM Nhanvien, PhongBan
WHERE PHG = MaPB AND TenPB LIKE ‘%Kinh doanh’ Phép kết ngoài lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt) SELECT
FROM LEFT | RIGHT | [OUTTER] JOIN
ON < iều kiện kết>
WHERE < iều kiện chọn dòng>
Ví dụ: Cho biết nhân viên và tên của phòng ban mà họ là trưởng phòng (nếu có)
SELECT HoNV, Tenlot, TenNV, TenPB
FROM NhanVien NV LEFT JOIN PhongBan PB lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt) ON NV.MaNV=PhongBan.TrgPhg
Ví dụ: Tìm họ tên nhân viên và tên thân nhân của họ (nếu có)
SELECT NV.MaNV, HoNV, TenLot, TenNV, TenTN, Quanhe
FROM ThanNhan TN RIGHT JOIN NhanVien NV lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt) ON TN.MaNV = NV.MaNV Truy vấn lồng nhau
– Một câu truy vấn (SELECT) lồng vào câu truy vấn khác gọi là
truy vấn lồng nhau hay Subquery lOMoARcPSD| 36991220
3.4. Truy vấn từ nhiều bảng (tt)
– Subquery ược bao bởi cặp dấu ngoặc (.......) và có thể lồng nhau nhiều mức.
– Subquery ược lồng vào sau từ khóa WHERE hoặc HAVING
– Câu truy vấn con thường trả về 1 tập các giá trị
– Nếu có nhiều truy vấn con sau WHERE thì thường ược kết
hợp với nhau bởi các phép toán logic lOMoARcPSD| 36991220 Truy vấn lồng nhau (tt) SELECT FROM Truy vấn cha WHERE ( SELECT FROM WHERE < iều kiện> ) Truy vấn con Các phép toán tập hợp – IN lOMoARcPSD| 36991220 Truy vấn lồng nhau (tt) – NOT IN – ALL – ANY / SOME – EXISTS – NOT EXISTS
Có 2 loại truy vấn lồng – Lồng phân cấp:
• Mệnh ề WHERE của Subquery không tham chiếu ến các thuộc
tính của các bảng trong mệnh ề FROM của truy vấn cha
• Khi thực hiện truy vấn con ược thực hiện trước lOMoARcPSD| 36991220 Truy vấn lồng nhau (tt) – Lồng tương quan:
• Mệnh ề WHERE của Subquery tham chiếu ến ít nhất 1 thuộc tính
của bảng trong mệnh ề FROM của truy vấn cha
• Khi thực hiện, câu truy vấn con ược thực nhiều lần – mỗi lần ứng
với 1 bộ của câu truy vấn cha lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
Ví dụ - Truy vấn lồng phân cấp
Đưa ra mã nhân viên, họ tên nhân viên thuộc phòng ‘Tài
chính’ hoặc phòng ‘Kế hoạch’
SELECT MaNV, HoNV, TenLot, TenNV FROM NHANVIEN WHERE Phg IN ( SELECT MaPHG FROM PHONGBAN lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
WHERE TenPB LIKE ‘%Tài chính’ OR TenPB LIKE ‘%Kế hoạch’ )
Hãy tìm những ề án có nhân viên họ ‘Nguyễn’ tham gia SELECT MaDA, TenDA FROM DEAN WHERE MaDA IN ( SELECT MaDA FROM PHANCONG PC, NHANVIEN NV WHERE PC.MaNV = NV.MaNV AND lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng phân cấp (tt) NV.HoNV LIKE ‘%Nguyễn%’ )
Hãy tìm những nhân viên không có thân nhân nào SELECT * SELECT * FROM NHANVIEN FROM NHANVIEN WHERE MaNV NOT IN ( WHERE MaNV <> ALL( SELECT MaNV SELECT MaNV FROM THANNHAN FROM THANNHAN ) ) lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
Tìm những nhân viên có lương lớn hơn lương của ít nhất một nhân viên phòng 4 SELECT * FROM NHANVIEN WHERE LUONG > ANY (
SELECT LUONG FROM NHANVIEN WHERE PhG = 4 )
Tìm những trưởng phòng có tối thiểu 1 thân nhân SELECT * FROM NHANVIEN lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng phân cấp (tt)
WHERE MANV IN (SELECT MaNV FROM THANNHAN) AND MANV IN (SELECT TRPHG FROM PHONGBAN)
Tìm những Nhân viên có lương cao hơn lương của mọi nhân viên phòng 4 lOMoARcPSD| 36991220
Truy vấn lồng tương quan
– Mệnh ề WHERE của Subquery tham chiếu ến ít nhất 1 thuộc tính
của bảng trong mệnh ề FROM của truy vấn cha
– Khi thực hiện, câu truy vấn con ược thực nhiều lần – mỗi lần ứng
với 1 bộ của câu truy vấn cha Ví dụ:
– Tìm những trưởng phòng có ít nhất 1 thân nhân SELECT * FROM NHANVIEN NV
WHERE EXISTS ( SELECT * FROM THANNHAN TN WHERE TN.MaNV = NV.MaNV)AND
EXISTS (SELECT TrgPHG FROM PHONGBAN WHERE TrgPHG = NV.MaNV) lOMoARcPSD| 36991220
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên không có thân nhân nào SELECT * FROM NHANVIEN NV WHERE NOT EXISTS ( SELECT * FROM THANNHAN TN WHERE TN.MaNV = NV.MaNV )
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên có lương cao hơn lương trung bình của phòng lOMoARcPSD| 36991220 SELECT * FROM NHANVIEN NV1 WHERE NV1.Luong > ( SELECT AVG(NV2.Luong) FROM NHANVIEN NV2 WHERE NV2.Phg = NV1.Phg )
Ví dụ - Truy vấn lồng lương quan (tt)
Tìm những nhân viên có lương bằng lương cao hơn lương
của ít nhất 1 nhân viên thuộc phòng ‘Tài chính’ SELECT * FROM NHANVIEN NV1 lOMoARcPSD| 36991220 WHERE EXISTS ( SELECT *
FROM NHANVIEN NV2, PHONGBAN PB WHERE NV2.Phg = PB.MaPB AND
PB.TenPB LIKE ‘%Tài chính%’AND NV1.Luong > NV2.Luong ) Nhận xét IN và EXISTS IN – IN
– Thuộc tính ở mệnh ề SELECT câu truy vấn con phải có cùng
kiểu dữ liệu với thuộc tính ở mệnh ề WHERE trong câu truy vấn cha lOMoARcPSD| 36991220 EXISTS
– Không cần có thuộc tính, hăng số hay biểu thức nàoứng trước
– Không cần liệt kê thuộc tính ở bệnh ề SELECT trong câu truy vấn con
– Những câu truy vấn có = ANY hoặc IN ều có thể chuyển thành câu truy vấn dùng EXISTS
Các phép toán tập hợp trong SQL Phép hợp: UNION lOMoARcPSD| 36991220
Phép giao: INTERSECT Phép trừ: EXCEPT Cú pháp: lOMoARcPSD| 36991220
Phép toán tập hợp trong SQL (tt)
Ví dụ: Tìm các mã ề án có nhân viên họ ‘Nguyễn’ tham gia
hoặc trưởng phòng chủ trì ề án có họ ‘Nguyễn’ SELECT MaDA FROM PHANCONG PC, NHANVIEN NV
WHERE (PC.MaNV = NV.MaNV) AND (HoNV = ‘Nguyễn’) UNION ( SELECT MaDA
FROM NHANVIEN NV, PHONGBAN PB, DEAN DA
WHERE (NV.MaNV=PB.TrgPhg) AND (DA.Phong= PB.MaPB)
AND (NV.HoNV = ‘Nguyễn’) ) lOMoARcPSD| 36991220
3.5. Các lệnh cập nhật dữ liệu Thêm 1 bộ (dòng) mới Sửa 1 bộ (dòng) Xóa 1 bộ (dòng)
Tạo mới 1 quan hệ (bảng) với nội dung từ CSDL lOMoARcPSD| 36991220
3.5.1. Thêm dòng vào bảng Cú pháp INSERT INTO ( ) VALUES () Ví dụ: lOMoARcPSD| 36991220
3.5.1. Thêm dòng vào bảng (tt)
Nhận xét lệnh INSERT INTO
– Thứ tự giá trị sau VALUES phải trùng với thứ tự cộtsau INSERT INTO
– Có thể thêm giá trị NULL ở thuộc tính không là khóa chính lOMoARcPSD| 36991220
– Lệnh INSERT INTO có thể không thực hiện ược (lỗi) nếu vi
phạm ràng buộc toàn vẹn (RBTV) • Khóa chính • Tham chiếu • Thuộc tính NOT NULL
3.5.1. Thêm dòng vào bảng (tt) Thêm nhiều dòng INSERT INTO ( ) Ví dụ: lOMoARcPSD| 36991220
3.5.2. Sửa dòng trong bảng Cú pháp UPDATE SET = , = , ……
[ WHERE < iều kiện> ] lOMoARcPSD| 36991220
Ví dụ: Tăng lương 30% cho những người là ‘nữ’ UPDATE NHANVIEN SET Luong = Luong*1.3, WHERE phai = ‘Nữ’
3.5.2. Sửa dòng trong bảng (tt) Nhận xét
– Lệnh UPDATE cập nhật những dòng thỏa iều kiệnsau WHERE
– Nếu không chỉ ịnh iều kiện sau WHERE thì mọi dòngtrong
bảng sẽ ược cập nhật giá trị mới
– Lệnh UPDATE có thể gây ra vi phạm RBTV lOMoARcPSD| 36991220 • Không cho chỉnh sửa
• Sửa luôn dòng có giá trị tham chiếu ến (CASCADE)
3.5.3. Xóa dòng trong bảng Cú pháp DELETE FROM WHERE < iều kiện> Ví dụ: lOMoARcPSD| 36991220 lOMoARcPSD| 36991220
3.5.3. Xóa dòng trong bảng (tt)
Ví dụ: Xóa i những nhân viên làm ở phòng ‘Dự án’ DELETE FROM NHANVIEN WHERE PHG IN ( SELECT MaPB FROM PHONGBAN
WHERE TenPB = ‘Phòng Dự án’ ) Nhận xét lOMoARcPSD| 36991220
3.5.3. Xóa dòng trong bảng (tt)
– Số dòng bị xóa phụ thuộc vào iều kiện sau WHERE
– Nếu không chỉ rõ iều kiện sau WHERE tất cả các dòng trong bảng sẽ bị xóa
– Lệnh DELETE FROM có thể gây ra vi phạm RBTV • Không cho xóa
• Xóa luôn những dòng có giá trị ang tham chiếu ến
• Đặt Null cho những giá trị tham chiếu lOMoARcPSD| 36991220
3.5.3. Xóa dòng trong bảng (tt) lOMoARcPSD| 36991220
3.5.4. Tạo bảng từ CSDL Cú pháp: SELECT FROM INTO WHERE <Điều kiện> GROUP BY
HAVING < iều kiện chọn nhóm> ORDER BY
3.5.4. Tạo bảng từ CSDL (tt) Ví dụ lOMoARcPSD| 36991220
SELECT PB.TenPB, AVG(NV.Luong) ASTBLuong FROM NHANVIEN NV, PHONGBAN PB INTO TABLE TBLUONG_PHONGBAN WHERE NV.PHG = PB.MaPB GROUP BY PHG, TenPB
3.6. Các lệnh liên quan cấu trúc CSDL
Các kiểu dữ liệu trong SQL Tên kiểu Ý nghĩa Char (w)
Kiểu ký tự ộ rộng cố ịnh (w) Varchar(w)
Kiểu ký tự có ộ rộng thay ổi 0 w Integer Kiểu số nguyên lOMoARcPSD| 36991220 Byte
Kiểu số nguyên nhỏ (0 255)
Số thực rộng w vị trí (cả dấu chấm) có s chữ số thập phân Numberic (w,s) Real, Double Số thực dấu phẩy ộng Float (n)
Số thực dấu phẩy ộng có ít nhất n chữ số Date Kiểu ngày tháng Time
Kiểu thời gian (giờ/phút/giây) Logical Kiểu logic (True/False)
3.6.1. Lệnh tạo bảng CREATE TABLE
Để ịnh nghĩa 1 bảng cần chỉ ra: – Tên bảng – Các thuộc tính • Tên thuộc tính lOMoARcPSD| 36991220 • Kiểu dữ liệu • RBTV trên thuộc tính Cú pháp lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) Ví dụ: lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) RBTV – NOT NULL – NULL – UNIQUE– DEFAUL – PRIMARY KEY – FOREIGN KEY / REFERENCES – CHECK Đặt tên cho RBTV Ví dụ RBTV lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) Ví dụ: lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) Ví dụ - ặt tên cho RBTV lOMoARcPSD| 36991220 3.6.1. CREATE TABLE (tt) Ví dụ - ặt tên cho RBTV lOMoARcPSD| 36991220
3.6.2. Lệnh sửa bảng (tt) 3.6.2. Lệnh sửa bảng Thay ổi cấu trúc bảng – Thêm cột mới – Xóa cột lOMoARcPSD| 36991220
3.6.2. Lệnh sửa bảng (tt) – Mở rộng cột Thay ổi RBTV – Thêm RBTV – Xóa RBTV
Ví dụ - thay ổi cấu trúc lOMoARcPSD| 36991220
3.6.2. Lệnh sửa bảng (tt) Ví dụ - thay ổi RBTV lOMoARcPSD| 36991220
3.6.2. Lệnh sửa bảng (tt) lOMoARcPSD| 36991220 3.6.3. Xóa bảng Cú pháp: Ví dụ: lOMoARcPSD| 36991220 3.6.3. Xóa bảng (tt) 3.8. Bài tập
1. Tìm những nhân viên làm việc ở phòng số 4
2. Tìm những nhân viên có mức lương trên 30000
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 87 lOMoARcPSD| 36991220
3. Tìm các nhân viên có mức lương trên 25,000 ở phòng 4 hoặc các nhân
viên có mức lương trên 30,000 ở phòng 5
4. Cho biết họ tên đầy đủ của các nhân viên có họ bắt đầu bằng ký tự ‘N’
5. Cho biết các nhân viên sinh trước năm 1975
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ, DIA_DIEM) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
1. Tìm những nhân viên làm việc ở phòng số 4
SELECT * FROM NHANVIEN WHERE PHG = 4
2. Tìm những nhân viên có mức lương trên 30000
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 88 lOMoARcPSD| 36991220
SELECT * FROM NHANVIEN WHERE LUONG > 30000
3. Tìm các nhân viên có mức lương trên 25,000 ở phòng 4 hoặc các nhân viên có
mức lương trên 30,000 ở phòng 5
SELECT * FROM NHANVIEN WHERE (LUONG > 25000 AND PHG = 4) OR (LUONG > 30000 AND PHG = 5)
4. Cho biết họ tên đầy đủ của các nhân viên có họ bắt đầu bằng ký tự ‘N’
SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE HONV LIKE N'N%'
5. Cho biết các nhân viên sinh trước năm 1975
SELECT * FROM NHANVIEN WHERE YEAR(NGSINH) < 1975
6. Cho biết các nhân viên sinh trước ngày 30/4/1975 hoặc ở TP HCM hoặc làm việc tại phòng số 4
7. Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng
8. Tìm tên những người trưởng phòng của từng phòng ban
9. Tìm tên và địa chỉ của tất cả các nhân viên của phòng "Nghiên cứu".
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 89 lOMoARcPSD| 36991220
10. Với mọi đề án ở "Ha Noi", liệt kê các mã số đề án (MADA), mã số phòng ban
chủ trì đề án (PHONG), họ tên trưởng phòng (HONV, TENLOT, TENNV) cũng như
địa chỉ (DCHI) và ngày sinh (NGSINH) của người ấy.
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
6. Cho biết các nhân viên sinh trước ngày 30/4/1975 hoặc ở TP HCM hoặc làm việc tại phòng số 4
SELECT * FROM NHANVIEN WHERE (YEAR(NGSINH) < 1975) OR (DCHI LIKE N'%Tp hcm') OR (PHG = 4)
7. Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng
SELECT P.TENPHG, D.DIADIEM FROM PHONGBAN P, DIADIEM_PHG D WHERE P.MAPHG = D.MAPHG
8. Tìm tên những người trưởng phòng của từng phòng ban
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 90 lOMoARcPSD| 36991220
SELECT TENNV FROM NHANVIEN N, PHONGBAN P WHERE N.MANV = P.TRPHG
9. Tìm tên và địa chỉ của tất cả các nhân viên của phòng "Nghiên cứu".
SELECT TENNV, DCHI FROM NHANVIEN N, PHONGBAN P WHERE N.PHG =
P.MAPHG AND P.TENPHG LIKE N'Nghiên cứu'
10. Với mọi đề án ở "Ha Noi", liệt kê các mã số đề án (MADA), mã số phòng
ban chủ trì đề án (PHONG), họ tên trưởng phòng (HONV, TENLOT, TENNV)
cũng như địa chỉ (DCHI) và ngày sinh (NGSINH) của người ấy.
SELECT D.MADA, D.PHONG, N.HONV, N.TENLOT, N.TENNV, N.DCHI, N.NGSINH
FROM NHANVIEN N, PHONGBAN P, DEAN D WHERE N.MANV = P.TRPHG AND
P.MAPHG = D.PHONG AND DDIEM_DA LIKE N'Hà Nội'
11. Cho biết tên các đề án mà nhân viên Đinh Bá Tiến đã tham gia
12. Cho biết số lượng đề án của công ty
13. Cho biết số lượng đề án do phòng 'Nghiên Cứu' chủ trì
14. Cho biết lương trung bình của các nữ nhân viên
15. Với mỗi nhân viên, cho biết số lượng nhân viên mà nhân viên đó quản lý trực tiếp.
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 91 lOMoARcPSD| 36991220
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA)
11. Cho biết tên các đề án mà nhân viên Đinh Bá Tiến đã tham gia
SELECT D.TENDA FROM NHANVIEN N, PHANCONG P, DEAN D WHERE N.MANV =
P.MA_NVIEN AND P.MADA = D.MADA AND (HONV + ' ' + TENLOT + ' ' + TENNV) = N'Đinh Bá Tiên'
12. Cho biết số lượng đề án của công ty
SELECT COUNT(MADA) AS SODEAN FROM DEAN
13. Cho biết số lượng đề án do phòng 'Nghiên Cứu' chủ trì
SELECT COUNT(D.MADA) AS SODEAN FROM DEAN D, PHONGBAN P WHERE D.PHONG
= P.MAPHG AND P.TENPHG LIKE N'Nghiên cứu'
14. Cho biết lương trung bình của các nữ nhân viên
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 92 lOMoARcPSD| 36991220
SELECT AVG(LUONG) AS LUONGTB FROM NHANVIEN WHERE PHAI LIKE N'Nữ'
15. Với mỗi nhân viên, cho biết số lượng nhân viên mà nhân viên đó quản lý trực tiếp.
SELECT Q.TENNV, COUNT(N.MANV) AS SONV FROM NHANVIEN N, NHANVIEN Q
WHERE N.MA_NQL = Q.MANV GROUP BY Q.TENNV
PHANCONG (MANV, MADA, THOIGIAN)
THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE) 93 lOMoARcPSD| 36991220
16. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của
những nhân viên làm việc cho phòng ban đó.
17. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì
18. Với mỗi phòng ban, cho biết tên phòng ban, họ tên người trưởng phòng và số
lượng đề án mà phòng ban đó chủ trì
19. Với mỗi phòng ban có mức lương trung bình lớn hơn 40,000, cho biết tên phòng
ban và số lượng đề án mà phòng ban đó chủ trì.
20. Cho biết số đề án diễn ra tại từng địa điểm
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
16. Với mỗi phòng ban, liệt kê tên phòng ban (TENPHG) và lương trung bình của
những nhân viên làm việc cho phòng ban đó. lOMoARcPSD| 36991220
SELECT P.TENPHG, AVG(N.LUONG) AS LUONGTB FROM NHANVIEN N, PHONGBAN P
WHERE N.PHG = P.MAPHG GROUP BY P.TENPHG
17. Với mỗi phòng ban, cho biết tên phòng ban và số lượng đề án mà phòng ban đó chủ trì
SELECT P.TENPHG, COUNT(D.MADA) AS SODEAN FROM PHONGBAN P, DEAN D
WHERE P.MAPHG = D.PHONG GROUP BY P.TENPHG
18. Với mỗi phòng ban, cho biết tên phòng ban, họ tên người trưởng phòng và số
lượng đề án mà phòng ban đó chủ trì
SELECT P.TENPHG, N.HONV, N.TENLOT, N.TENNV, COUNT(D.MADA) AS SODEAN
FROM PHONGBAN P, DEAN D, NHANVIEN N WHERE P.MAPHG = D.PHONG AND
P.TRPHG = N.MANV GROUP BY P.TENPHG, N.HONV, N.TENLOT, N.TENNV
19. Với mỗi phòng ban có mức lương trung bình lớn hơn 40,000, cho biết tên phòng
ban và số lượng đề án mà phòng ban đó chủ trì.
SELECT P.TENPHG, COUNT(D.MADA) AS SODEAN FROM NHANVIEN N, PHONGBAN P,
DEAN D WHERE P.MAPHG = N.PHG AND P.MAPHG = D.PHONG GROUP BY P.TENPHG
HAVING AVG(N.LUONG) > 40000
20. Cho biết số đề án diễn ra tại từng địa điểm
SELECT DDIEM_DA, COUNT(DDIEM_DA) AS SODEAN FROM DEAN GROUP BY DDIEM_DA
21. Cho biết danh sách các đề án (MADA) có: nhân công với họ (HONV) là ‘Dinh’ hoặc ,
có người trưởng phòng chủ trì đề án với họ (HONV) là ‘Dinh’. lOMoARcPSD| 36991220
22. Danh sách những nhân viên (HONV, TENLOT, TENNV) có trên 2 thân nhân.
23. Danh sách những nhân viên (HONV, TENLOT, TENNV) không có thân nhân nào.
24. Danh sách những trưởng phòng (HONV, TENLOT, TENNV) có tối thiểu một thân nhân.
25. Tìm họ (HONV) của những trưởng phòng chưa có gia đình.
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
21. Cho biết danh sách các đề án (MADA) có: nhân công với họ (HONV) là ‘Dinh’ hoặc ,
có người trưởng phòng chủ trì đề án với họ (HONV) là ‘Dinh’.
SELECT TENDA FROM DEAN WHERE MADA IN (SELECT D.MADA FROM DEAN D,
PHANCONG P, NHANVIEN N WHERE D.MADA = P.MADA AND P.MA_NVIEN = N.MANV
AND N.HONV = N'Đinh’) OR MADA IN (SELECT D.MADA FROM DEAN D, PHONGBAN B,
NHANVIEN N WHERE D.PHONG = B.MAPHG AND B.TRPHG = N.MANV AND N.HONV = N'Đinh')
22. Danh sách những nhân viên (HONV, TENLOT, TENNV) có trên 2 thân nhân. lOMoARcPSD| 36991220
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, THANNHAN T WHERE
N.MANV = T.MA_NVIEN GROUP BY N.HONV, N.TENLOT, N.TENNV HAVING COUNT(T.MA_NVIEN) > 2
23. Danh sách những nhân viên (HONV, TENLOT, TENNV) không có thân nhân nào.
SELECT HONV, TENLOT, TENNV FROM NHANVIEN WHERE MANV NOT IN (SELECT
N.MANV FROM NHANVIEN N, THANNHAN T WHERE N.MANV = T.MA_NVIEN GROUP BY N.MANV)
24. Danh sách những trưởng phòng (HONV, TENLOT, TENNV) có tối thiểu một thân nhân.
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHONGBAN P, THANNHAN
T WHERE P.TRPHG = N.MANV AND N.MANV = T.MA_NVIEN GROUP BY N.HONV, N.TENLOT, N.TENNV
25. Tìm họ (HONV) của những trưởng phòng chưa có gia đình.
SELECT N.HONV FROM NHANVIEN N, PHONGBAN P WHERE P.TRPHG = N.MANV AND
N.MANV NOT IN (SELECT N.MANV FROM NHANVIEN N, THANNHAN T WHERE N.MANV = T.MA_NVIEN GROUP BY N.MANV)
26. Danh sách những nhân viên (HONV, TENLOT, TENNV) làm việc trong mọi đề án của công ty
27. Danh sách những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án do phòng số 4 chủ trì. lOMoARcPSD| 36991220
28. Tìm những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án mà
nhân viên Đinh Bá Tiến làm việc
NHANVIEN (MANV,HONV, TENLOT, TENNV, PHAI, LUONG, DIACHI, NGAYSINH,
MA_NQL, PHG) PHONGBAN (MAPB, TENPB, TRPHG, NGAYBĐ) DIADIEM_PHG (MAPB, DIADIEM)
DEAN (MADA, TENDA, NGAYBD, PHONG, DIADIEM_DA) PHANCONG (MANV,
MADA, THOIGIAN) THANNHAN (MANV,TENTN,PHAI,NGAYSINH,QUANHE)
26. Danh sách những nhân viên (HONV, TENLOT, TENNV) làm việc trong mọi đề án của công ty
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHANCONG P WHERE
N.MANV = P.MA_NVIEN GROUP BY N.HONV, N.TENLOT, N.TENNV HAVING
COUNT(DISTINCT P.MADA) = (SELECT COUNT(D.MADA) FROM DEAN D)
27. Danh sách những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án do phòng số 4 chủ trì. lOMoARcPSD| 36991220
SELECT N.HONV, N.TENLOT, N.TENNV FROM DEAN D, CONGVIEC C, PHANCONG P,
NHANVIEN N WHERE D.MADA = C.MADA AND C.MADA = P.MADA AND C.STT = P.STT
AND P.MA_NVIEN = N.MANV AND D.PHONG = 4 GROUP BY N.HONV, N.TENLOT,
N.TENNV HAVING COUNT(DISTINCT P.MADA) = (SELECT COUNT(MADA) FROM DEAN WHERE PHONG = 4)
28. Tìm những nhân viên (HONV, TENLOT, TENNV) được phân công tất cả đề án mà
nhân viên Đinh Bá Tiến làm việc
SELECT N.HONV, N.TENLOT, N.TENNV FROM NHANVIEN N, PHANCONG P WHERE
N.MANV = P.MA_NVIEN GROUP BY N.MANV, N.HONV, N.TENLOT, N.TENNV HAVING
COUNT(DISTINCT P.MADA) = (SELECT COUNT(DISTINCT PC.MADA) FROM NHANVIEN
NV, PHANCONG PC WHERE NV.MANV = PC.MA_NVIEN AND (NV.HONV + ' ' +
NV.TENLOT + ' ' + NV.TENNV) = N'Đinh Bá Tiên')