Bài giảng Cơ sở dữ liệu: Chương 3 - ThS. Trần Quang Hải Bằng

Chia sẻ: May Trời Gio Bien | Ngày: | Loại File: PDF | Số trang:43

Thêm vào BST

Báo xấu

91
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng "Cơ sở dữ liệu - Chương 3: Ngôn ngữ truy vấn SQL" cung cấp cho người đọc các kiến thức: Khái quát về ngôn ngữ truy vấn dữ liệu, câu lệnh SELECT, các hàm thao tác dữ liệu, truy vấn thông tin từ nhiều bảng, các lệnh cập nhật dữ liệu,... Mời các bạn cùng tham khảo.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Cơ sở dữ liệu: Chương 3 - ThS. Trần Quang Hải Bằng

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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 2
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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 3 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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 4
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… bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 5 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) bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 6
3.2. lệnh SELECT – Truy vấn dữ liệu Gồm 3 mệnh đề cơ bản SELECT FROM WHERE – • 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: >,
Ví dụ Lấy tất cả các cột SELECT * của quan hệ FROM NHANVIEN WHERE PHG=5 σ PHG=5(NHANVIEN) bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 9 Mệnh đề SELECT bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 10
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))) bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 11 Mệnh đề SELECT (tt) Mở rộng: SELECT MANV, HONV +’ ‘+ TENLOT+’ ‘ TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 12
Mệnh đề SELECT (tt) Mở rộng: SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 13 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 WHERE PHG=5 AND PHAI=‘Nam’ bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 14
Ví dụ Cho biết MANV và TENNV làm việc ở phòng ‘Nghien cuu’ SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPGH=‘Nghien cuu’ bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 15 Mệnh đề WHERE Biểu thức luận lý SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE PHG=MAPHG AND TENPHG=‘Nghien cuu’ TRUE TRUE bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 16
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 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE (TENPHG=‘Nghien cuu’ OR TENPHG=‘Quan ly’) AND PHG=MAPHG bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 17 Mệnh đề WHERE (tt) BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=2000000 AND LUONG
Mệnh đề WHERE (tt) IN NOT IN SELECT MANV, TENNV SELECT MANV, TENNV FROM NHANVIEN FROM NHANVIEN WHERE PHG IN (4,5) WHERE PHG NOT IN (4,5) SELECT MANV, TENNV FROM NHANVIEN WHERE PHG = 4 OR PHG=5 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 19 Mệnh đề WHERE (tt) LIKE – Lấy tất cả chuỗi giống với mẫ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 %’ Nhiều ký tự bất kỳ NOT LIKE – Lấy tất cả những chuỗi không giống bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 20
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) 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 Chương 3 - Ngôn ngữ truy vấn SQL 21 Mệnh đề WHERE (tt) Không sử dụng WHERE – Kết quả là phép tích “đề-các” bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 22
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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 23 Mệnh đề ORDER BY Dùng để hiển thị kết quả truy vấn theo thứ tự Cú pháp: SELECT FROM WHERE ORDER BY – ASC: Sắp xếp tăng (mặc định) – DESC: Sắp xếp giảm bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 24
Mệnh đề ORDER BY (tt) Ví dụ: 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 Chương 3 - Ngôn ngữ truy vấn SQL 25 Gom nhóm - GROUP BY Cú pháp: SELECT FROM WHERE GROUP BY 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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 26
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 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 Chương 3 - Ngôn ngữ truy vấn SQL 27 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 MaNV So DA Tong TG 999888777 10 32.5 SELECT MaNV, COUNT(*) AS ‘So DA’, 999888777 30 10 SUM(THOIGIAN) AS ‘Tong TG’ 888777666 10 10 FROM PHANCONG 888777666 30 10 GROUP BY MaNV 333222111 10 35.7 333222111 20 25 333222111 30 5 SELECT NV.MaNV, TENV, COUNT(*) AS ‘So DA’, SUM(THOIGIAN) AS ‘Tong TG’ MaNV So DA Tong TG FROM PHANCONG PC, NHANVIEN NV 999888777 2 42.5 WHERE PC.MaNV = NV.MaNV 888777666 2 20 GROUP BY PC.MaNV, TenNV 333222111 3 65.7 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 28
Mệnh đề HAVING Ví dụ: Hãy tìm những nhân viên tham gia từ 2 đề án trở lên. MaNV So DA Tong TG 999888777 10 32.5 999888777 30 10 888777666 10 10 888777666 30 10 333222111 10 35.7 Bị loại ra 333222111 20 25 333222111 30 5 555444333 30 15 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 29 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 GROUP BY HAVING Ví dụ: – Tìm mã những nhân viên tham gia nhiều hơn 2 Đề án SELECT MaNV, COUNT(*) AS ‘So DA’, FROM PHANCONG GROUP BY MaNV HAVING COUNT (*) > 2 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 30
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 "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 Chương 3 - Ngôn ngữ truy vấn SQL 31 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,… bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 32
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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 33 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. bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 34
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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 35 3.4. Truy vấn từ nhiều bảng (tt) Phép kết tự nhiên SELECT FROM WHERE Hoặc SELECT FROM INNER JOIN ON WHERE 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 bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 36
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’ bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 37 3.4. Truy vấn từ nhiều bảng (tt) Phép kết ngoài SELECT FROM LEFT | RIGHT | [OUTTER] JOIN ON WHERE bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 38
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ó) SELECT HoNV, Tenlot, TenNV, TenPB FROM NhanVien NV LEFT JOIN PhongBan PB ON NV.MaNV=PhongBan.TrgPhg bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 39 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ó) SELECT NV.MaNV, HoNV, TenLot, TenNV, TenTN, Quanhe FROM ThanNhan TN RIGHT JOIN NhanVien NV ON TN.MaNV = NV.MaNV bangtqh@utc2.edu.vn Chương 3 - Ngôn ngữ truy vấn SQL 40