-
Thông tin
-
Hỏi đáp
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!
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')