Bài giảng Cơ sở dữ liệu (Databases) - Chương 3: Ngôn ngữ truy vấn SQL

bangtqh@utc2.edu.vn CƠ SỞ DỮ LIỆU ( Databases ) Chương 3: Ngôn ngữ truy vấn SQL bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 2 bangtqh@utc2.edu.vn 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ế.
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 Chương 3 - Ngôn ngữ truy vấn SQL 3 bangtqh@utc2.edu.vn 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)
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 Chương 3 - Ngôn ngữ truy vấn SQL 4 bangtqh@utc2.edu.vn 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 – 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 Chương 3 - Ngôn ngữ truy vấn SQL 5 bangtqh@utc2.edu.vn Lược đồ CSDL sử dụng 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) Chương 3 - Ngôn ngữ truy vấn SQL 6 bangtqh@utc2.edu.vn 3.2. lệnh SELECT – Truy vấn dữ liệu
Gồm 3 mệnh đề cơ bản – • 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 – • 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 Chương 3 - Ngôn ngữ truy vấn SQL 7 SELECT FROM WHERE bangtqh@utc2.edu.vn 3.2. lệnh SELECT (tt)
SQL và Đại số quan hệ Chương 3 - Ngôn ngữ truy vấn SQL 8 SELECT FROM WHERE pi x σ SELECT L FROM R WHERE C piL(σC(R )) bangtqh@utc2.edu.vn Ví dụ σPHG=5(NHANVIEN) Chương 3 - Ngôn ngữ truy vấn SQL 9 SELECT * FROM NHANVIEN WHERE PHG=5 Lấy tất cả các cột của quan hệ bangtqh@utc2.edu.vn Mệnh đề SELECT Chương 3 - Ngôn ngữ truy vấn SQL 10 bangtqh@utc2.edu.vn 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(piMaNV,HONV,TENLOT,TENNVσPHG=5∧PHAI=‘Nam’(NHANVIEN))) Chương 3 - Ngôn ngữ truy vấn SQL 11 bangtqh@utc2.edu.vn Mệnh đề SELECT (tt)
Mở rộng: Chương 3 - Ngôn ngữ truy vấn SQL 12 SELECT MANV, HONV +’ ‘+ TENLOT+’ ‘ TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Mệnh đề SELECT (tt)
Mở rộng: Chương 3 - Ngôn ngữ truy vấn SQL 13 SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Mệnh đề SELECT (tt)
Loại bỏ các dòng trùng nhau Chương 3 - Ngôn ngữ truy vấn SQL 14 SELECT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ SELECT DISTINCT LUONG FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Ví dụ
Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’ Chương 3 - Ngôn ngữ truy vấn SQL 15 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPGH=‘Nghien cuu’ bangtqh@utc2.edu.vn Mệnh đề WHERE Chương 3 - Ngôn ngữ truy vấn SQL 16 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPHG=‘Nghien cuu’ Biểu thức luận lý TRUE TRUE bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
Độ ưu tiên – Viết trước thực hiện trước – Trong ngoặc thực hiện trước Chương 3 - Ngôn ngữ truy vấn SQL 17 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
BETWEEN
NOT BETWEEN Chương 3 - Ngôn ngữ truy vấn SQL 18 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=2000000 AND LUONG<=3000000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 2000000 AND 3000000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 2000000 AND 3000000 bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
IN Chương 3 - Ngôn ngữ truy vấn SQL 19
NOT IN SELECT MANV, TENNV FROM NHANVIEN WHERE PHG IN (4,5) SELECT MANV, TENNV FROM NHANVIEN WHERE PHG = 4 OR PHG=5 SELECT MANV, TENNV FROM NHANVIEN WHERE PHG NOT IN (4,5) bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
LIKE – Lấy tất cả chuỗi giống với mẫu
NOT LIKE – Lấy tất cả những chuỗi không giống Chương 3 - Ngôn ngữ truy vấn SQL 20 SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen_ _ _ _’ SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen %’ 1 Ký tự bất kỳ Nhiều ký tự bất kỳ bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
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) Chương 3 - Ngôn ngữ truy vấn SQL 21 SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NULL SELECT MANV, TENNV FROM NHANVIEN WHERE MA_NQL IS NOT NULL bangtqh@utc2.edu.vn Mệnh đề WHERE (tt)
Không sử dụng WHERE – Kết quả là phép tích “đề-các” Chương 3 - Ngôn ngữ truy vấn SQL 22 bangtqh@utc2.edu.vn Mệnh đề FROM
Tên, Bí danh Chương 3 - Ngôn ngữ truy vấn SQL 23 SELECT TENPHG, DIADIEM FROM PHONGBAN, DDIEM_PHG WHERE MAPHG=MAPHG SELECT TENPHG, DIADIEM FROM PHONGBAN, DDIEM_PHG WHERE PhongBan.MAPHG=DDiem_PHG.MAPHG Bí danh bangtqh@utc2.edu.vn Mệnh đề ORDER BY
Dùng để hiển thị kết quả truy vấn theo thứ tự
Cú pháp: – ASC: Sắp xếp tăng (mặc định) – DESC: Sắp xếp giảm Chương 3 - Ngôn ngữ truy vấn SQL 24 SELECT FROM WHERE ORDER BY bangtqh@utc2.edu.vn Mệnh đề ORDER BY (tt)
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 25 SELECT MANV, MADA FROM PHANCONG ORDER BY MANV DESC, MADA MANV MADA 999888777 10 999888777 30 888777666 10 888777666 30 333222111 10 333222111 20 333222111 30 bangtqh@utc2.edu.vn Gom nhóm - GROUP BY
Cú pháp:
Mỗi bộ trong nhóm sẽ có cùng giá trị tại các thuộc tính gom nhó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 Chương 3 - Ngôn ngữ truy vấn SQL 26 SELECT FROM WHERE GROUP BY bangtqh@utc2.edu.vn Mệnh đề GROUP BY (tt)
Ví dụ: Cho biết số lượng nhân viên ở mỗi phòng ban Chương 3 - Ngôn ngữ truy vấn SQL 27 SELECT PHG, COUNT(*) AS ‘So NV’ FROM NHANVIEN GROUP BY PHG Hoặc SELECT T ENPHG, COUNT(*) AS ‘So NV’ FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG GROUP BY TENPHG bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 28 SELECT MaNV, COUNT(*) AS ‘So DA’, SUM(THOIGIAN) AS ‘Tong TG’ FROM PHANCONG GROUP BY MaNV SELECT NV.MaNV, TENV, COUNT(*) AS ‘So DA’, SUM(THOIGIAN) AS ‘Tong TG’ FROM PHANCONG PC, NHANVIEN NV WHERE PC.MaNV = NV.MaNV GROUP BY PC.MaNV, TenNV MaNV So DA Tong TG 999888777 10 32.5 999888777 30 10 888777666 10 10 888777666 30 10 333222111 10 35.7 333222111 20 25 333222111 30 5 MaNV So DA Tong TG 999888777 2 42.5 888777666 2 20 333222111 3 65.7 bangtqh@utc2.edu.vn Mệnh đề HAVING
Ví dụ: Hãy tìm những nhân viên tham gia từ 2 đề án trở lên. Chương 3 - Ngôn ngữ truy vấn SQL 29 MaNV So DA Tong TG 999888777 10 32.5 999888777 30 10 888777666 10 10 888777666 30 10 333222111 10 35.7 333222111 20 25 333222111 30 5 555444333 30 15 Bị loại ra bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 30 SELECT FROM WHERE GROUP BY HAVING SELECT MaNV, COUNT(*) AS ‘So DA’, FROM PHANCONG GROUP BY MaNV HAVING COUNT (*) > 2 bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 31 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 "TB Luong" FROM NhanVien AS NV, PhongBan AS PB WHERE NV.Phg = PB.MaPB GROUP BY NV.PHG, PB.TenPB HAVING AVG(LUONG) > 3000000; bangtqh@utc2.edu.vn 3.3. 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, Chương 3 - Ngôn ngữ truy vấn SQL 32 bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 33 bangtqh@utc2.edu.vn 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 – 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. Chương 3 - Ngôn ngữ truy vấn SQL 34 bangtqh@utc2.edu.vn 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) – Truy vấn lồng nhau Chương 3 - Ngôn ngữ truy vấn SQL 35 bangtqh@utc2.edu.vn 3.4. Truy vấn từ nhiều bảng (tt)
Phép kết tự nhiên Hoặc Ví dụ: Đưa ra danh sách nhân viên và tên phòng làm việc Chương 3 - Ngôn ngữ truy vấn SQL 36 SELECT FROM WHERE SELECT HoNV, Tenlot, TenNV, TenPB FROM Nhanvien, PhongBan WHERE PHG = MaPB SELECT FROM INNER JOIN ON WHERE bangtqh@utc2.edu.vn 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’ Chương 3 - Ngôn ngữ truy vấn SQL 37 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’ bangtqh@utc2.edu.vn 3.4. Truy vấn từ nhiều bảng (tt)
Phép kết ngoài Chương 3 - Ngôn ngữ truy vấn SQL 38 SELECT FROM LEFT | RIGHT | [OUTTER] JOIN ON WHERE bangtqh@utc2.edu.vn 3.4. Truy vấn từ nhiều bảng (tt)
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ó) Chương 3 - Ngôn ngữ truy vấn SQL 39 SELECT HoNV, Tenlot, TenNV, TenPB FROM NhanVien NV LEFT JOIN PhongBan PB ON NV.MaNV=PhongBan.TrgPhg bangtqh@utc2.edu.vn 3.4. Truy vấn từ nhiều bảng (tt)
Ví dụ: Tìm họ tên nhân viên và tên thân nhân của họ (nếu có) Chương 3 - Ngôn ngữ truy vấn SQL 40 SELECT NV.MaNV, HoNV, TenLot, TenNV, TenTN, Quanhe FROM ThanNhan TN RIGHT JOIN NhanVien NV ON TN.MaNV = NV.MaNV bangtqh@utc2.edu.vn 3.4. Truy vấn từ nhiều bảng (tt)
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 – 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 Chương 3 - Ngôn ngữ truy vấn SQL 41 bangtqh@utc2.edu.vn Truy vấn lồng nhau (tt) Chương 3 - Ngôn ngữ truy vấn SQL 42 Truy vấn con Truy vấn cha SELECT FROM WHERE ( SELECT FROM WHERE ) bangtqh@utc2.edu.vn Truy vấn lồng nhau (tt)
Các phép toán tập hợp – IN – NOT IN – ALL – ANY / SOME – EXISTS – NOT EXISTS Chương 3 - Ngôn ngữ truy vấn SQL 43 bangtqh@utc2.edu.vn Truy vấn lồng nhau (tt)
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 – 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 Chương 3 - Ngôn ngữ truy vấn SQL 44 bangtqh@utc2.edu.vn 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’ Chương 3 - Ngôn ngữ truy vấn SQL 45 SELECT MaNV, HoNV, TenLot, TenNV FROM NHANVIEN WHERE Phg IN ( SELECT MaPHG FROM PHONGBAN WHERE TenPB LIKE ‘%Tài chính’ OR TenPB LIKE ‘%Kế hoạch’ ) bangtqh@utc2.edu.vn Ví dụ - Truy vấn lồng phân cấp (tt)
Hãy tìm những đề án có nhân viên họ ‘Nguyễn’ tham gia Chương 3 - Ngôn ngữ truy vấn SQL 46 SELECT MaDA, TenDA FROM DEAN WHERE MaDA IN ( SELECT MaDA FROM PHANCONG PC, NHANVIEN NV WHERE PC.MaNV = NV.MaNV AND NV.HoNV LIKE ‘%Nguyễn%’ ) bangtqh@utc2.edu.vn Ví dụ - Truy vấn lồng phân cấp (tt)
Hãy tìm những nhân viên không có thân nhân nào Chương 3 - Ngôn ngữ truy vấn SQL 47 SELECT * FROM NHANVIEN WHERE NOT IN ( SELECT MaNV FROM THANNHAN ) SELECT * FROM NHANVIEN WHERE MaNV ALL ( SELECT MaNV FROM THANNHAN ) bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 48 SELECT * FROM NHANVIEN WHERE LUONG > ANY ( SELECT LUONG FROM NHANVIEN WHERE PhG = 4 ) bangtqh@utc2.edu.vn Ví dụ - Truy vấn lồng phân cấp (tt)
Tìm những trưởng phòng có tối thiểu 1 thân nhân
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 Chương 3 - Ngôn ngữ truy vấn SQL 49 SELECT * FROM NHANVIEN WHERE MANV IN (SELECT MaNV FROM THANNHAN) AND MANV IN (SELECT TRPHG FROM PHONGBAN) bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 50 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) bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 51 SELECT * FROM NHANVIEN NV WHERE NOT EXISTS ( SELECT * FROM THANNHAN TN WHERE TN.MaNV = NV.MaNV ) bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 52 SELECT * FROM NHANVIEN NV1 WHERE NV1.Luong > ( SELECT AVG(NV2.Luong) FROM NHANVIEN NV2 WHERE NV2.Phg = NV1.Phg ) bangtqh@utc2.edu.vn 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’ Chương 3 - Ngôn ngữ truy vấn SQL 53 SELECT * FROM NHANVIEN NV1 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 ) bangtqh@utc2.edu.vn 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
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 Chương 3 - Ngôn ngữ truy vấn SQL 54 bangtqh@utc2.edu.vn Các phép toán tập hợp trong SQL
Phép hợp: UNION
Phép giao: INTERSECT
Phép trừ: EXCEPT
Cú pháp: Chương 3 - Ngôn ngữ truy vấn SQL 55 bangtqh@utc2.edu.vn 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’ Chương 3 - Ngôn ngữ truy vấn SQL 56 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’) ) bangtqh@utc2.edu.vn 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 Chương 3 - Ngôn ngữ truy vấn SQL 57 bangtqh@utc2.edu.vn 3.5.1. Thêm dòng vào bảng
Cú pháp
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 58 INSERT INTO ( ) VALUES () bangtqh@utc2.edu.vn 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ột sau INSERT INTO – Có thể thêm giá trị NULL ở thuộc tính không là khóa chính – 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 Chương 3 - Ngôn ngữ truy vấn SQL 59 bangtqh@utc2.edu.vn 3.5.1. Thêm dòng vào bảng (tt)
Thêm nhiều dòng
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 60 INSERT INTO ( ) bangtqh@utc2.edu.vn 3.5.2. Sửa dòng trong bảng
Cú pháp
Ví dụ: Tăng lương 30% cho những người là ‘nữ’ Chương 3 - Ngôn ngữ truy vấn SQL 61 UPDATE SET = , = , [ WHERE ] UPDATE NHANVIEN SET Luong = Luong*1.3, WHERE phai = ‘Nữ’ bangtqh@utc2.edu.vn 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ện sau WHERE – Nếu không chỉ định điều kiện sau WHERE thì mọi dòng trong bảng sẽ được cập nhật giá trị mới – Lệnh UPDATE có thể gây ra vi phạm RBTV • Không cho chỉnh sửa • Sửa luôn dòng có giá trị tham chiếu đến (CASCADE) Chương 3 - Ngôn ngữ truy vấn SQL 62 bangtqh@utc2.edu.vn 3.5.3. Xóa dòng trong bảng
Cú pháp
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 63 DELETE FROM WHERE bangtqh@utc2.edu.vn 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’ Chương 3 - Ngôn ngữ truy vấn SQL 64 DELETE FROM NHANVIEN WHERE PHG IN ( SELECT MaPB FROM PHONGBAN WHERE TenPB = ‘Phòng Dự án’ ) bangtqh@utc2.edu.vn 3.5.3. Xóa dòng trong bảng (tt)
Nhận xét – 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 Chương 3 - Ngôn ngữ truy vấn SQL 65 bangtqh@utc2.edu.vn 3.5.3. Xóa dòng trong bảng (tt) Chương 3 - Ngôn ngữ truy vấn SQL 66 bangtqh@utc2.edu.vn 3.5.4. Tạo bảng từ CSDL
Cú pháp: Chương 3 - Ngôn ngữ truy vấn SQL 67 SELECT FROM INTO WHERE GROUP BY HAVING ORDER BY bangtqh@utc2.edu.vn 3.5.4. Tạo bảng từ CSDL (tt)
Ví dụ SELECT PB.TenPB, AVG(NV.Luong) AS TBLuong FROM NHANVIEN NV, PHONGBAN PB INTO TABLE TBLUONG_PHONGBAN WHERE NV.PHG = PB.MaPB GROUP BY PHG, TenPB Chương 3 - Ngôn ngữ truy vấn SQL 68 bangtqh@utc2.edu.vn 3.6. Các lệnh liên quan cấu trúc CSDL
Các kiểu dữ liệu trong SQL Chương 3 - Ngôn ngữ truy vấn SQL 69 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 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) bangtqh@utc2.edu.vn 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 • Kiểu dữ liệu • RBTV trên thuộc tính
Cú pháp Chương 3 - Ngôn ngữ truy vấn SQL 70 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 71 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
RBTV – NOT NULL – NULL – UNIQUE – DEFAUL – PRIMARY KEY – FOREIGN KEY / REFERENCES – CHECK
Đặt tên cho RBTV Chương 3 - Ngôn ngữ truy vấn SQL 72 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
Ví dụ RBTV Chương 3 - Ngôn ngữ truy vấn SQL 73 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 74 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
Ví dụ - đặt tên cho RBTV Chương 3 - Ngôn ngữ truy vấn SQL 75 bangtqh@utc2.edu.vn 3.6.1. CREATE TABLE (tt)
Ví dụ - đặt tên cho RBTV Chương 3 - Ngôn ngữ truy vấn SQL 76 bangtqh@utc2.edu.vn 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 – Mở rộng cột Chương 3 - Ngôn ngữ truy vấn SQL 77 bangtqh@utc2.edu.vn 3.6.2. Lệnh sửa bảng (tt)
Thay đổi RBTV – Thêm RBTV – Xóa RBTV Chương 3 - Ngôn ngữ truy vấn SQL 78 bangtqh@utc2.edu.vn 3.6.2. Lệnh sửa bảng (tt)
Ví dụ - thay đổi cấu trúc Chương 3 - Ngôn ngữ truy vấn SQL 79 bangtqh@utc2.edu.vn 3.6.2. Lệnh sửa bảng (tt)
Ví dụ - thay đổi RBTV Chương 3 - Ngôn ngữ truy vấn SQL 80 bangtqh@utc2.edu.vn 3.6.3. Xóa bảng
Cú pháp:
Ví dụ: Chương 3 - Ngôn ngữ truy vấn SQL 81 bangtqh@utc2.edu.vn 3.6.3. Xóa bảng (tt) Chương 3 - Ngôn ngữ truy vấn SQL 82 bangtqh@utc2.edu.vn 3.7. Lệnh giao quyền truy cập CSDL (Đọc tài liệu) Chương 3 - Ngôn ngữ truy vấn SQL 83 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 84 bangtqh@utc2.edu.vn 3.8. Bài tập (Tài liệu, trang 59 – 62) Chương 3 - Ngôn ngữ truy vấn SQL 85