Cơ sở dữ liệu - Bài 3: Ngôn ngữ sql

CƠ SỞ DỮ LIỆU Bài 3: NGÔN NGỮ SQL 06/08/2012 1 HVĐ – THQL VẤN ĐỀ • Làm sao tương tác với hệ quản trị CSDL để – Tạo CSDL – Cập nhật dữ liệu – Quản lý người dùng – Khai thác CSDL 06/08/2012 HVĐ – THQL 2 MỤC TIÊU • Kiến thức – Ngôn ngữ con định nghĩa dữ liệu (DDL) – Ngôn ngữ con thao tác dữ liệu (DML) – Ngôn ngữ con điều khiển truy xuất (ACL) – Ngôn ngữ con truy vấn (QL) • Kỹ năng – Cài đặt cơ sở dữ liệu – Cập nhật dữ liệu – Quản lý người dùng – Giải bài toán dùng ngôn ngữ con truy vấn • Yêu cầu: áp dụng thực tế 06/08/2012 HVĐ – THQL 3 NỘI DUNG • Ngôn ngữ con định nghĩa dữ liệu • Ngôn ngữ con điều khiển truy cập • Ngôn ngữ con thao tác dữ liệu • Ngôn ngữ con truy vấn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 4 TẠO CƠ SỞ DỮ LIỆU • Ngôn ngữ con định nghĩa dữ liệu – Định nghĩa miền giá trị – Tạo cấu trúc bảng – Khai báo các ràng buộc – Định nghĩa khung nhìn • Ngôn ngữ con điều khiển truy cập • Ngôn ngữ con thao tác dữ liệu • Ngôn ngữ con truy vấn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 5 ĐỊNH NGHĨA MIỀN GIÁ TRỊ • Các ví dụ: – CREATE DOMAIN tid AS char(6) not null – CREATE DOMAIN tdiem AS int check (value > 0) • Thực hành với một hệ quản trị CSDL cụ thể – Tuổi – Ngày sinh – … 06/08/2012 6 HVĐ – THQL TẠO CẤU TRÚC BẢNG • Các ví dụ – CREATE TABLE gvien( gvid tid, gvten char(10), nsinh date ) – CREATE TABLE monhoc( monid tid primary key, monten char(10), sotc int not null check (sotc between 3 and 4)) • Thực hành với một hệ quản trị CSDL cụ thể 06/08/2012 7 HVĐ – THQL KHAI BÁO RÀNG BUỘC • Các ví dụ – ALTER TABLE gvien ADD CONSTRAINT gvien_pkey primary key(gvid) – ALTER TABLE phancong ADD CONSTRAINT pc_gv_fk foreign key (gvid) references gvien(gvid) • Thực hành với một hệ quản trị CSDL cụ thể 06/08/2012 8 HVĐ – THQL ĐỊNH NGHĨA KHUNG NHÌN • Các ví dụ – CREATE VIEW class3 AS SELECT * FROM student WHERE classID = ‘C3’ WITH CHECK OPTION – CREATE VIEW siSo (classID, className, siSo) AS SELECT class. classID, className, count(*) FROM student, class WHERE student.classID = class.classID GROUP BY classID, className • Thực hành với một hệ quản trị CSDL cụ thể 06/08/2012 HVĐ – THQL 9 NN ĐỊNH NGHĨA DỮ LIỆU (DDL) • Lệnh –Create –Alter –Drop • Đối tượng –Database –Domain –Table –Constraint –Function –Trigger –View 06/08/2012 10 HVĐ – THQL TÌNH HUỐNG • Mô hình cơ sở dữ liệu Bán Hàng – Miền giá trị – Bảng – Khung nhìn – Ràng buộc 06/08/2012 HVĐ – THQL 11 QUẢN LÝ NGƯỜI DÙNG • Ngôn ngữ con định nghĩa dữ liệu • Ngôn ngữ con điều khiển truy cập – Tạo tài khoản người dùng – Cấp quyền – Hủy quyền • Ngôn ngữ con thao tác dữ liệu • Ngôn ngữ con truy vấn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 12 TẠO TÀI KHOẢN NGƯỜI DÙNG • Các ví dụ (PostgreSQL): – CREATE ROLE hvduc LOGIN PASSWORD ‘duc’ SUPERUSER CREATEDB NOCREATEROLE; – GRANT guest TO hvduc; • Thực hành với một hệ quản trị CSDL cụ thể 06/08/2012 13 HVĐ – THQL CẤP QUYỀN • Các ví dụ (PostgreSQL) – GRANT ALL ON TABLE gvien TO hvduc; – GRANT SELECT ON TABLE gvien TO guest; – GRANT EXECUTE ON FUNCTION checksiso() TO postgres; • Thực hành với một hệ quản trị CSDL cụ thể 06/08/2012 14 HVĐ – THQL HUỶ QUYỀN • Dùng REVOKE để huỷ quyền • Dùng DROP để loại bỏ tài khoản người dùng 06/08/2012 15 HVĐ – THQL TÌNH HUỐNG • Mô hình cơ sở dữ liệu Bán Hàng – Phân loại người dùng – Tạo tài khoản người dùng – Cấp quyền 06/08/2012 HVĐ – THQL 16 QUẢN LÝ DỮ LIỆU • Ngôn ngữ con định nghĩa dữ liệu • Ngôn ngữ con điều khiển truy cập • Ngôn ngữ con thao tác dữ liệu – Thêm – Xóa – Sửa • Ngôn ngữ con truy vấn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 17 CÁC VÍ DỤ • INSERT INTO gvien VALUES (‘hat’, ‘tuan’, ‘5-24-1958), (‘hvd’, ‘duc’, ‘12-23-1963’); • DELETE FROM gvien WHERE gvid=‘hat’; • UPDATE gvien SET nsinh=nsinh+7 WHERE gvid=‘hvd’ 06/08/2012 18 HVĐ – THQL TÌNH HUỐNG • Mô hình cơ sở dữ liệu Bán Hàng – Cập nhập dữ liệu 06/08/2012 HVĐ – THQL 19 KHAI THÁC CƠ SỞ DỮ LIỆU • Ngôn ngữ con định nghĩa dữ liệu • Ngôn ngữ con điều khiển truy cập • Ngôn ngữ con thao tác dữ liệu • Ngôn ngữ con truy vấn – Truy vấn – Cài đặt đại số quan hệ – Cài đặt ràng buộc toàn vẹn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 20 TRUY VẤN • Cấu trúc chính: SELECT FROM WHERE • Kết nhiều bảng INNER JOINT / LEFT JOINT / RIGHT JOIN ON • Các phép so sánh – θ – θ ANY/ALL – EXISTS 06/08/2012 21 HVĐ – THQL CÁC PHÉP TOÁN TẬP CON • SELECT * FROM r1 UNION SELECT* FROM r2 • SELECT * FROM r1 INTERSECT SELECT * FROM r2 • SELECT * FROM r1 EXCEPT SELECT * FROM r2 • Áp dụng – Ghi sổ kép kế toán – Toàn bộ lịch giảng – Tìm những giảng viên không có giờ giảng 06/08/2012 22 HVĐ – THQL TÍNH TOÁN • Từ các giá trị thuộc tính của mỗi dòng • Tập các giá trị từng thuộc tính trong cùng một nhóm (nhóm theo dòng) – Count – Sum – Avg – Max – … 06/08/2012 23 HVĐ – THQL CẤU TRÚC ĐẦY ĐỦ • Cấu trúc: SELECT FROM WHERE GROUP BY HAVING • Ví dụ tìm các hoá đơn trị giá hơn 1000 trong năm 2009 SELECT HDSo, SUM(SoL*GiaB) AS TriG FROM HDon INNER JOIN CTHDon ON (HDon.HDSo = CTHDon.HDSo) WHERE month(NgayL) = 2009 GROUP BY CTHDon.HDSo HAVING TriG > 1000 06/08/2012 24 HVĐ – THQL TÌNH HUỐNG • Cơ sở dữ liệu Thể thao đội 06/08/2012 HVĐ – THQL 25 TÌNH HUỐNG • Tính tiền điện 06/08/2012 HVĐ – THQL 26 QUAN HỆ GIỮA QL VÀ RA Tính tổng số tiết của mỗi giảng viên giảng cho lớp hc12 • SELECT gvien.gvid, gvten, sum(sotc*15) as tongtiet FROM (gvien g INNER JOIN phancong p ON g.gvid = p.gvid ) INNER JOIN monhoc m ON p.monid=m.monid WHERE lopid=‘hc12’ GROUP BY g.gvid • Πgvid, gvten, tt[gvidG tt =sum(sotc*15)(g ⋈ σlopid=‘hc12’( p) ⋈ m)] • Các biểu thức con – r1 = Πgvid, sotc(σlopid=‘hc12’(p) ⋈ m) – r2 = gvidG tongtiet =sum(sotc*15)(r1) – r = Πgvid, gvten, tongtiet(r2 ⋈ g)] 06/08/2012 27 HVĐ – THQL CÀI ĐẶT ĐẠI SỐ QUAN HỆ • Các phép toán tập con • Các phép toán quan hệ 06/08/2012 HVĐ – THQL 28 CÀI ĐẶT RÀNG BUỘC TOÀN VẸN • Phát biểu mệnh đề • Chuyển mệnh đề thành biểu thức quan hệ • Ràng buộc tồn tại • Một số ví dụ khác 06/08/2012 HVĐ – THQL 29 LẬP TRÌNH PHIÁ SERVER • Viết hàm, thủ tục • Cài đặt ràng buộc toàn vẹn • Cài đặt các xử lý phức tạp như chuyển khoản, tương tranh,… Ta cần 2 khái niệm: – Giao tác: hàm ý một hành động hoặc được thực hiện hoàn toàn hoặc không có gì xảy ra. – Khoá (lock). 06/08/2012 30 HVĐ – THQL VÍ DỤ • T1 đọc (35) • T1 cộng (135) • T1 ghi (135) • T2 đọc (135) • T2 trừ (105) • T2 ghi (105) • T1 đọc (35) • T2 đọc (35) • T1 cộng (135) • T2 trừ (5) • T1 ghi (135) • T2 ghi (5) • Chuyển khoản begin; update bankacct set bal = bal – 100 where ano=‘12345’; update bankacct set bal = bal + 100 where ano=‘54321’; commit; • Xét x = 35, T1 thêm 100 vào x, T2 bớt 30 từ x • T1 đọc (35) • T1 cộng (135) • T1 ghi (135) • T1 ROLLBACK • T2 đọc (35) • T2 trừ (5) • T2 ghi (5) • T1 đọc (35) • T1 cộng (135) • T1 ghi (135) • T2 đọc (135) • T2 trừ (105) • T1 ROLLBACK • T2 ghi (105) 06/08/2012 31 HVĐ – THQL TRIGGER • Ngữ cảnh của ràng buộc là quan hệ giữa – các bảng và – các thao tác insert, delete, update. • Viết trigger với mục đích cài đặt các ràng buộc toàn vẹn • Ở đây ta quan tâm đến bài toán truy vấn: tìm ra các vi phạm, nếu có • Lưu ý sử dụng các biến quan hệ có sẵn (inserted) 06/08/2012 HVĐ – THQL 32 VÍ DỤ 06/08/2012 33 HVĐ – THQL TRỞ LẠI MỤC TIÊU • Kiến thức – Ngôn ngữ con định nghĩa dữ liệu (DDL) – Ngôn ngữ con thao tác dữ liệu (DML) – Ngôn ngữ con điều khiển truy xuất (ACL) – Ngôn ngữ con truy vấn (QL) • Kỹ năng – Cài đặt cơ sở dữ liệu – Quản lý dữ liệu – Quản lý người dùng – Giải bài toán dùng ngôn ngữ con truy vấn • Yêu cầu: áp dụng thực tế 06/08/2012 HVĐ – THQL 34 THẢO LUẬN TÌNH HUỐNG • Ngôn ngữ con định nghĩa dữ liệu • Ngôn ngữ con điều khiển truy cập • Ngôn ngữ con thao tác dữ liệu • Ngôn ngữ con truy vấn • Thảo luận tình huống 06/08/2012 HVĐ – THQL 35 CÔNG TY MỸ GIA • Cài đặt CSDL • Phân loại người dùng • Cập nhật dữ liệu • Truy vấn 06/08/2012 HVĐ – THQL 36 KẾT LUẬN • Dùng DDL để cài đặt CSDL, trong đó khung nhìn cho phép thực hiện an toàn và toàn vẹn dữ liệu. • Dùng ACL để quản lý người dùng • Dùng DML để cập nhật dữ liệu • Dùng QL để truy vấn dữ liệu, cài đặt RBTV • Dùng QL lưu ý đến quan hệ, đặc biệt lưu ý đến cú pháp so sánh 06/08/2012 HVĐ – THQL 37