Bài giảng CSDL - Chương 4: SQL

NV
FROM NHANVIEN
GROUP BY PHG
SELECT TENPHG, COUNT(*) AS SL_NV
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY TENPHG
Cơ sở dữ liệu - Khoa CNTT 97
Ví dụ 15
 Với mỗi nhân viên cho biết mã số, họ tên, số lượng
đề án và tổng thời gian mà họ tham gia
SODA THOIGIAN
1 32.5
2 7.5
123456789
123456789
MA_NVIEN
2 10.0
3 10.0
333445555
333445555
10 10.0333445555
20 20.0
10 35.0
888665555
987987987
30 5.0987987987
30 20.0987654321
20 15.0987654321
1 20.0453453453
2 20.0453453453
SELECT MA_NVIEN, COUNT(*) AS SL_DA, 
SUM(THOIGIAN) AS TONG_TG
FROM PHANCONG
GROUP BY MA_NVIEN
SELECT HONV, TENNV, COUNT(*) AS SL_DA, 
SUM(THOIGIAN) AS TONG_TG
FROM PHANCONG, NHANVIEN
WHERE MA_NVIEN=MANV
GROUP BY MA_NVIEN, HONV, TENNV
Cơ sở dữ liệu - Khoa CNTT 98
Ví dụ 16
 Cho biết những nhân viên tham gia từ 2 đề án trở
lên
SODA THOIGIAN
1 32.5
2 7.5
123456789
123456789
MA_NVIEN
2 10.0
3 10.0
333445555
333445555
10 10.0333445555
20 20.0
10 35.0
888665555
987987987
30 5.0987987987
30 20.0987654321
20 15.0987654321
1 20.0453453453
2 20.0453453453
bị loại ra
Cơ sở dữ liệu - Khoa CNTT 99
Điều kiện trên nhóm
 Cú pháp
SELECT 
FROM 
WHERE 
GROUP BY 
HAVING 
Cơ sở dữ liệu - Khoa CNTT 100
Ví dụ 16
 Cho biết những nhân viên tham gia từ 2 đề án trở
lên
SELECT MA_NVIEN
FROM PHANCONG
GROUP BY MA_NVIEN
HAVING COUNT(*) >= 2
Cơ sở dữ liệu - Khoa CNTT 101
Ví dụ 17
 Cho biết những phòng ban (TENPHG) có lương
trung bình của các nhân viên lớn lơn 20000
SELECT PHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHG
HAVING AVG(LUONG) > 20000
SELECT TENPHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY TENPHG
HAVING AVG(LUONG) > 20000
Cơ sở dữ liệu - Khoa CNTT 102
Nhận xét
 Mệnh đề GROUP BY
- Các thuộc tính trong mệnh đề SELECT (trừ những thuộc
tính trong các hàm kết hợp) phải xuất hiện trong mệnh
đề GROUP BY
 Mệnh đề HAVING
- Sử dụng các hàm kết hợp trong mệnh đề SELECT để
kiểm tra một số điều kiện nào đó
- Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc
trên từng bộ
- Sau khi gom nhóm điều kiện trên nhóm mới được thực
hiện
Cơ sở dữ liệu - Khoa CNTT 103
Nhận xét (tt)
 Thứ tự thực hiện câu truy vấn có mệnh đề GROUP
BY và HAVING
- (1) Chọn ra những dòng thỏa điều kiện trong mệnh đề
WHERE
- (2) Những dòng này sẽ được gom thành nhiều nhóm
tương ứng với mệnh đề GROUP BY
- (3) Áp dụng các hàm kết hợp cho mỗi nhóm
- (4) Bỏ qua những nhóm không thỏa điều kiện trong
mệnh đề HAVING
- (5) Rút trích các giá trị của các cột và hàm kết hợp trong
mệnh đề SELECT
Cơ sở dữ liệu - Khoa CNTT 104
Ví dụ 18
 Tìm những phòng ban có lương trung bình cao nhất
SELECT PHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHG
HAVING MAX(AVG(LUONG))
SELECT PHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHG
HAVING AVG(LUONG) >= ALL (
SELECT AVG(LUONG)
FROM NHANVIEN
GROUP BY PHG)
Cơ sở dữ liệu - Khoa CNTT 105
Ví dụ 19
SELECT TENNV
FROM NHANVIEN NV1
WHERE 2 >= (
SELECT COUNT(*)
FROM NHANVIEN NV2
WHERE NV2.LUONG>NV1.LUONG )
 Tìm 3 nhân viên có lương cao nhất
Cơ sở dữ liệu - Khoa CNTT 106
Ví dụ 12
 Tìm tên các nhân viên được phân công làm tất cả
các đồ án
SELECT MANV, TENVN
FROM NHANVIEN, PHANCONG
WHERE MANV=MA_NVIEN
GROUP BY MANV, TENNV
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM DEAN )
Cơ sở dữ liệu - Khoa CNTT 107
Nội dung chi tiết
 Giới thiệu
 Định nghĩa dữ liệu
 Truy vấn dữ liệu
- Truy vấn cơ bản
- Tập hợp, so sánh tập hợp và truy vấn lồng
- Hàm kết hợp và gom nhóm
- Một số dạng truy vấn khác
 Cập nhật dữ liệu
 Khung nhìn (view)
 Chỉ mục (index)
Cơ sở dữ liệu - Khoa CNTT 108
Một số dạng truy vấn khác
 Truy vấn con ở mệnh đề FROM
 Điều kiện kết ở mệnh đề FROM
- Phép kết tự nhiên
- Phép kết ngoàI
 Cấu trúc CASE
Cơ sở dữ liệu - Khoa CNTT 109
Truy vấn con ở mệnh đề FROM
 Kết quả trả về của một câu truy vấn phụ là một bảng
- Bảng trung gian trong quá trình truy vấn
- Không có lưu trữ thật sự
 Cú pháp
SELECT 
FROM R1, R2, () AS tên_bảng
WHERE 
Cơ sở dữ liệu - Khoa CNTT 110
Ví dụ 18
 Cho biết những phòng ban (TENPHG) có lương
trung bình của các nhân viên lớn lơn 20000
SELECT PHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHG
HAVING AVG(LUONG) > 20000
SELECT PHG, TENPHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY PHG, TENPHG
HAVING AVG(LUONG) > 20000
Ví dụ 18
Cơ sở dữ liệu - Khoa CNTT 111
SELECT TENPHG, TEMP.LUONG_TB
FROM PHONGBAN, (SELECT PHG, AVG(LUONG) AS LUONG_TB
FROM NHANVIEN
GROUP BY PHG
HAVING AVG(LUONG)> 20000 ) AS TEMP
WHERE MAPHG=TEMP.PHG
Hoặc
Cơ sở dữ liệu - Khoa CNTT 112
Điều kiện kết ở mệnh đề FROM
 Kết bằng
 Kết ngoài
SELECT 
FROM R1 [INNER] JOIN R2 ON 
WHERE 
SELECT 
FROM R1 LEFT|RIGHT [OUTER] JOIN R2 ON 
WHERE 
Cơ sở dữ liệu - Khoa CNTT 113
Ví dụ 20
SELECT MANV, TENNV 
FROM NHANVIEN, PHONGBAN
WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG
SELECT MANV, TENNV 
FROM NHANVIEN INNER JOIN PHONGBAN ON PHG=MAPHG
WHERE TENPHG=‘Nghien cuu’
 Tìm mã và tên các nhân viên làm việc tại phòng
‘Nghien cuu’
Cơ sở dữ liệu - Khoa CNTT 114
Ví dụ 21
 Cho biết họ tên nhân viên và tên phòng ban mà họ
là trưởng phòng nếu có
TENNV HONV TENPHG
Tung Nguyen Nghien cuu
Hang Bui null
Nhu Le null
Vinh Pham Quan ly
SELECT TENNV, HONV, TENPHG
FROM NHANVIEN, PHONGBAN
WHERE MANV=TRPHG
Cơ sở dữ liệu - Khoa CNTT 115
Ví dụ 21 (tt)
TENNV HONV TENPHG
Tung Nguyen Nghien cuu
Hang Bui null
Nhu Le null
Vinh Pham Quan ly
SELECT TENNV, HONV, TENPHG
FROM NHANVIEN LEFT JOIN PHONGBAN ON MANV=TRPHG
Mở rộng dữ liệu cho 
bảng NHANVIEN
PHONGBANNHANVIEN join
MANV=TRPHG
Ví dụ 21 (tt)
Cơ sở dữ liệu - Khoa CNTT 116
NHANVIENPHONGBAN join
TRPHG=MANV
SELECT TENNV, HONV, TENPHG
FROM PHONGBAN RIGHT JOIN NHANVIEN ON MANV=TRPHG
Cơ sở dữ liệu - Khoa CNTT 117
Ví dụ 22
SELECT NV.TENNV, NV.TENDA
FROM (PHANCONG PC JOIN DEAN DA ON SODA=MADA)
RIGHT JOIN NHANVIEN NV ON PC.MA_NVIEN=NV.MANV
 Tìm họ tên các nhân viên và tên các đề án nhân
viên tham gia nếu có
NHANVIENPHANCONG join DEAN
MA_NVIEN=MANV
join
Cơ sở dữ liệu - Khoa CNTT 118
Cấu trúc CASE
 Cho phép kiểm tra điều kiện và xuất thông tin theo
từng trường hợp
 Cú pháp
CASE 
WHEN THEN 
WHEN THEN 
…
[ELSE ]
END
Cơ sở dữ liệu - Khoa CNTT 119
Ví dụ 23
 Cho biết họ tên các nhân viên đã đến tuổi về hưu
(nam 60 tuổi, nữ 55 tuổi)
SELECT HONV, TENNV
FROM NHANVIEN
WHERE YEAR(GETDATE()) – YEAR(NGSINH) >= ( CASE PHAI
WHEN 'Nam' THEN 60
WHEN 'Nu' THEN 55
END )
Cơ sở dữ liệu - Khoa CNTT 120
Ví dụ 24
 Cho biết họ tên các nhân viên và năm về hưu
SELECT HONV, TENNV,
(CASE PHAI 
WHEN 'Nam' THEN YEAR(NGSINH) + 60
WHEN 'Nu‘ THEN YEAR(NGSINH) + 55
END ) AS NAMVEHUU
FROM NHANVIEN
Cơ sở dữ liệu - Khoa CNTT 121
Kết luận
SELECT 
FROM 
[WHERE ]
[GROUP BY ]
[HAVING ]
[ORDER BY ]
Cơ sở dữ liệu - Khoa CNTT 122
Khung nhìn
 Bảng là một quan hệ được tổ chức lưu trữ vật lý
trong CSDL
 Khung nhìn cũng là một quan hệ
- Không được lưu trữ vật lý (bảng ảo)
- Không chứa dữ liệu
- Được định nghĩa từ những bảng khác
- Có thể truy vấn hay cập nhật thông qua khung nhìn
Cơ sở dữ liệu - Khoa CNTT 123
Khung nhìn (tt)
 Tại sao phải sử dụng khung nhìn?
- Che dấu tính phức tạp của dữ liệu
- Đơn giản hóa các câu truy vấn
- Hiển thị dữ liệu dưới dạng tiện dụng nhất
- An toàn dữ liệu
Cơ sở dữ liệu - Khoa CNTT 124
Định nghĩa khung nhìn
 Cú pháp
 Bảng ảo này có
- Danh sách thuộc tính trùng với các thuộc tính trong
mệnh đề SELECT
- Số dòng phụ thuộc vào điều kiện ở mệnh đề WHERE
- Dữ liệu được lấy từ các bảng ở mệnh đề FROM
CREATE VIEW AS
DROP VIEW 
Cơ sở dữ liệu - Khoa CNTT 125
Ví dụ
CREATE VIEW NV_P5 AS
SELECT MANV, HONV, TENLOT, TENVN
FROM NHANVIEN
WHERE PHG=5
CREATE VIEW TONGLNG_SLNV_PB AS
SELECT MAPHG, TENPHG, COUNT(*) AS SLNV, 
SUM(LUONG) AS TONGLNG 
FROM NHANVIEN, PHONGBAN
WHERE PHG=MAPHG
GROUP BY TENPHG, MAPHG
Cơ sở dữ liệu - Khoa CNTT 126
Truy vấn trên khung nhìn
 Tuy không chứa dữ liệu nhưng có thể thực hiện các
câu truy vấn trên khung nhìn
SELECT TENNV
FROM NV_P5
WHERE HONV LIKE ‘Nguyen’
NV_P5 MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN))
 TENNV (HONV=‘Nguyen’ (NV_P5))
Cơ sở dữ liệu - Khoa CNTT 127
Truy vấn trên khung nhìn (tt)
 Có thể viết câu truy vấn dữ liệu từ khung nhìn và
bảng
SELECT HONV, TENVN, TENDA, THOIGIAN
FROM NV_P5, PHANCONG, DEAN
WHERE MANV=MA_NVIEN AND SODA=MADA
NV_P5 MANV,HONV, TENLOT, TENNV (PHG=5 (NHANVIEN))
TMP  NV_P5 MANV=MA_NVIEN PHONGBAN SODA=MADADEAN
TENNV,TENDA,THOIGIAN(TMP)
Cơ sở dữ liệu - Khoa CNTT 128
Cập nhật trên khung nhìn
 Có thể dùng các câu lệnh INSERT, DELETE và
UPDATE cho các khung nhìn đơn giản
- Khung nhìn được xây dựng trên 1 bảng và có khóa
chính của bảng
 Không thể cập nhật dữ liệu nếu
- Khung nhìn có dùng từ khóa DISTINCT
- Khung nhìn có sử dụng các hàm kết hợp
- Khung nhìn có mệnh đề SELECT mở rộng
- Khung nhìn được xây dựng từ bảng có RB trên cột
- Khung nhìn được xây dựng từ nhiều bảng
Cơ sở dữ liệu - Khoa CNTT 129
Cập nhật trên khung nhìn (tt)
 Sửa lại họ cho nhân viên mã ‘123456789’ ở phòng 5
là ‘Pham’
UPDATE NV_P5
SET HONV=‘Pham’
WHERE MANV= ‘123456789’
Cơ sở dữ liệu - Khoa CNTT 130
Chỉ mục
 Chỉ mục trên thuộc tính A là một cấu trúc dữ liệu
làm cho việc tìm kiếm mẫu tin có chứa A hiệu quả
hơn
SELECT *
FROM NHANVIEN
WHERE PHG=5 AND PHAI=‘Nu’
Bảng NHANVIEN có 10.000 bộ
Có 200 nhân viên làm việc cho phòng 5
Đọc 10.000 bộ
Đọc 200 bộ
Đọc 70 bộ
Cơ sở dữ liệu - Khoa CNTT 131
Chỉ mục (tt)
 Cú pháp
 Ví dụ
CREATE INDEX ON ()
CREATE INDEX PHG_IND ON NHANVIEN(PHG)
CREATE INDEX PHG_PHAI_IND ON NHANVIEN(PHG, PHAI)
DROP INDEX 
Cơ sở dữ liệu - Khoa CNTT 132
Chỉ mục (tt)
 Nhận xét
- Tìm kiếm nhanh trong trường hợp so sánh với hằng số
và phép kết
- Làm chậm đi các thao tác thêm, xóa và sửa
- Tốn chi phí
 Lưu trữ chỉ mục
 Truy xuất đĩa nhiều
 Chọn lựa cài đặt chỉ mục hợp lý???