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

43 trang ngocly 5530

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng môn Cơ sở dữ liệu - Chương 3: Ngôn ngữ truy vấn SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_giang_mon_co_so_du_lieu_chuong_3_ngon_ngu_truy_van_sql.pdf

Nội dung text: Bài giảng môn Cơ sở dữ liệu - Chương 3: Ngôn ngữ truy vấn SQL

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 QUE ry 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: >, =, =, x FROM WHERE σ SELECT L FROM R πL(σC(R )) WHERE C bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 8
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<=3000000 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 2000000 AND 3000000 NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 2000000 AND 3000000 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 18
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 ề x y – SQRT : Tính c ăn b ậc 2 – LOG : Tính Log t ự nhiên – EXP : Tính e x – 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
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 41 Truy v ấn l ồng nhau (tt) SELECT FROM Truy v ấn cha WHERE ( SELECT FROM WHERE ) Truy v ấn con bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 42
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 43 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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 44
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 WHERE TenPB LIKE ‘%Tài chính’ OR TenPB LIKE ‘%K ế ho ạch’ ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 45 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 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 Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 46
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 SELECT * SELECT * FROM NHANVIEN FROM NHANVIEN WHERE NOT IN ( WHERE MaNV ANY ( SELECT LUONG FROM NHANVIEN WHERE PhG = 4 ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 48
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 SELECT * FROM NHANVIEN 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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 49 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) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 50
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 ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 51 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 SELECT * FROM NHANVIEN NV1 WHERE NV1.Luong > ( SELECT AVG(NV2.Luong) FROM NHANVIEN NV2 WHERE NV2.Phg = NV1.Phg ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 52
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 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 Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 53 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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 54
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: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 55 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’) ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 56
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 57 3.5.1. Thêm dòng vào bảng Cú pháp INSERT INTO ( ) VALUES ( ) Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 58
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 59 3.5.1. Thêm dòng vào bảng (tt) Thêm nhi ều dòng INSERT INTO ( ) Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 60
3.5.2. Sửa dòng trong bảng Cú pháp UPDATE SET = , = , [ WHERE ] Ví d ụ: Tăng l ươ ng 30% cho nh ững ng ườ i là ‘n ữ’ UPDATE NHANVIEN SET Luong = Luong*1.3, WHERE phai = ‘N ữ’ bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 61 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) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 62
3.5.3. Xóa dòng trong bảng Cú pháp DELETE FROM WHERE Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 63 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’ ) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 64
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 65 3.5.3. Xóa dòng trong bảng (tt) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 66
3.5.4. Tạo bảng t ừ CSDL Cú pháp: SELECT FROM INTO WHERE GROUP BY HAVING ORDER BY bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 67 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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 68
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 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 Numberic (w,s) 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 Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 69 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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 70
3.6.1. CREATE TABLE (tt) Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 71 3.6.1. CREATE TABLE (tt) RBTV – NOT NULL – NULL – UNIQUE – DEFAUL – PRIMARY KEY – FOREIGN KEY / REFERENCES – CHECK Đặ t tên cho RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 72
3.6.1. CREATE TABLE (tt) Ví d ụ RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 73 3.6.1. CREATE TABLE (tt) Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 74
3.6.1. CREATE TABLE (tt) Ví d ụ - đặ t tên cho RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 75 3.6.1. CREATE TABLE (tt) Ví d ụ - đặ t tên cho RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 76
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 bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 77 3.6.2. Lệnh s ửa bảng (tt) Thay đổ i RBTV – Thêm RBTV – Xóa RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 78
3.6.2. Lệnh s ửa bảng (tt) Ví d ụ - thay đổ i c ấu trúc bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 79 3.6.2. Lệnh s ửa bảng (tt) Ví d ụ - thay đổ i RBTV bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 80
3.6.3. Xóa bảng Cú pháp: Ví d ụ: bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 81 3.6.3. Xóa bảng (tt) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 82
3.7. L ệnh giao quy ền truy c ập CSDL (Đọ c tài li ệu) bangtqh@utc2.edu.vn 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
3.8. Bài t ập (Tài li ệu, trang 59 – 62) bangtqh@utc2.edu.vn Ch ươ ng 3 - Ngôn ng ữ truy v ấn SQL 85