Phần 3: Ngôn ngữ sql

Trường Đại học Công nghệ thông tin Bộ môn Hệ thống thông tin Môn học: Hệ quản trị CSDL Oracle GV: Dương Khai Phong Email: [email protected] 1 Nội dung môn học: 1. Giới thiệu Oracle 2. Các công cụ cơ bản trong Oracle • Công cụ Enterprise Manager (EM) • Công cụ SQL Plus • Công cụ iSQLPlus 3. Ngôn ngữ SQL 4. Ngôn ngữ thủ tục PL/SQL 5. Kiến trúc quản trị Oracle 2 Phần 3: NGÔN NGỮ SQL 3 Ngôn ngữ SQL – Giới thiệu  Là ngôn ngữ chuẩn để truy vấn và thao tác trên CSDL quan hệ  Là ngôn ngữ phi thủ tục  Khởi nguồn của SQL là SEQUEL - Structured English Query Language, năm 1974)  Các chuẩn SQL › SQL89 (SQL1) › SQL92 (SQL2) › SQL99 (SQL3) 4 Ngôn ngữ SQL – Phân loại  Ngôn ngữ định nghĩa dữ liệu: tạo table (bảng), view (khung nhìn), sửa cấu trúc table và thêm, xóa RBTV, xóa table, xóa view, đổi tên table  Ngôn ngữ thao tác dữ liệu: thêm, xóa, sửa dữ liệu, và truy vấn dữ liệu.  Ngôn ngữ điều khiển dữ liệu: tạo quyền hạn, xóa quyền, làm cho hiệu lực/mất hiệu lực quyền, tạo người dùng, đổi mật khẩu, xóa người dùng, cấ́p quyền và thu hồi quyền sử dụng trên cơ sở dữ liệu.  Ngoài ra còn có các lệnh điều khiển giao tác.  Lệnh thao tác trên các thành phần CSDL khác: Synonym, Index và Sequence 5 Ngôn ngữ SQL Ngôn ngữ định nghĩa dữ liệu  Ngôn ngữ định nghĩa dữ liệu (DDL– Data Definition Language)  Bao gồm: › Lệnh tạo table, tạo view (CREATE...) › Lệnh sửa cấu trúc table, thêm, xóa ràng buộc toàn vẹn trên table (ALTER...) › Lệnh xóa table, xóa view (DROP...) › Đổi tên table (RENAME…) 6 Phần 3: Ngôn ngữ SQL Ngôn ngữ thao tác dữ liệu  Ngôn ngữ thao tác dữ liệu (DML – Data Manipulation Language)  Bao gồm: › Lệnh thêm dữ liệu (INSERT...) › Lệnh sửa dữ liệu (UPDATE...) › Lệnh xóa dữ liệu (DELETE...) › Truy vấn dữ liệu (SELECT...) 7 Phần 3: Ngôn ngữ SQL Ngôn ngữ điều khiển dữ liệu  Ngôn ngữ điều khiển dữ liệu (DCL – Data Control Language). Bao gồm: › Lệnh tạo quyền hạn (Create Role…) › Lệnh thiết lập, đổi hay bỏ mật khẩu của role (Alter Role…) › Lệnh xóa quyền hạn (Drop Role…) › Lệnh tạo người dùng, đổi mật khẩu và xóa người dùng (Create User…, Alter User…, Drop User…) › Lệnh cấp quyền cho người sử dụng cơ sở dữ liệu (GRANT...) › Lệnh thu hồi quyền hạn của người sử dụng cơ sở dữ liệu (REVOKE...) 8 Phần 3: Ngôn ngữ SQL Các lệnh điều khiển giao tác + các đối tượng khác  Lệnh điều khiển giao tác bao gồm: › Lệnh COMMIT, lệnh ROLLBACK, lệnh SAVEPOINT, lệnh AUTOCOMMIT  Các đối tượng khác: › SYNONYM: tạo một Synonym (Create Synonym…), xóa Synnonym (Drop Synonym…) › INDEX: tạo chỉ mục cho table (Create Index…) , bảo đảm giá trị duy nhất trong cột, thường là giá trị Primary key. › SEQUENCE: tạo giá trị SEQUENCE cho cột (Create Sequence…). 9 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU TẠO BẢNG, VIEW - CREATE  Cú pháp tạo TABLE: CREATE TABLE ( tên_cột1 kiểu_dữ_liệu [not null], tên_cột2 kiểu_dữ_liệu [not null], … tên_cộtn kiểu_dữ_liệu [not null], khai báo khóa chính, khóa ngoại, ràng buộc toàn vẹn ) 10 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU TẠO BẢNG, VIEW - CREATE  Cú pháp tạo VIEW: CREATE [OR REPLACE] [FORCE] VIEW [tên-cột1, cột2,…] AS SELECT … [Điều kiện] [Ràng buộc] - Ghi chú: tùy chọn Replace sẽ xóa view và tạo view mới nếu view đã tồn tại rồi. 11 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU TẠO BẢNG, VIEW – KIỂU DỮ LIỆU Loại dữ liệu Mô tả VARCHAR2(n) Dữ liệu kiểu ký tự, n<=4000 CHAR(n) Dữ liệu kiểu ký tự, kích thước cố đinh, n<=2000 NUMBER Kiểu số nguyên, số ký số tối đa là 38 ký số NUMBER(p) Kiểu số nguyên, với số ký số tối đa là p NUMBER(p,s) Kiểu số thực, tối đa p ký số, s số thập phân. p≤38, -84 ≤ s ≤ 127.Ví dụ: số 7456123, khai báo kiểu number (7, -2) = 7456100 DATE Kiểu ngày, lưu ngày từ 1/1/4712 BC -> 31/12/9999 LONG Kiểu số nguyên RAW Chuỗi nhị phân dài tối đa 2000 bytes LONG RAW Chuỗi nhị phân dài tối đa 2GB BLOB (Bynary Large Object) có độ dài ≤ 4GB CLOB (Character Large Object) có độ dài ≤ 4GB BFILE Chứa con trỏ chỉ đến một tập tin nhị phân ở ngoài DB 12 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU TẠO BẢNG, VIEW – VÍ DỤ Cho lược đồ CSDL “quản lý đề án cty” như sau NHANVIEN (MaNV, HoNV, TenLot, TenNV, Phai, Luong, Phong, NgaySinh, DiaChi, Ma_NQL) PHONGBAN (MaPHG, TenPHG, TrPHG, NG_NhanChuc) DEAN (MaDA, TenDA, DDIEM_DA, Phong) PHANCONG (MaNV, MaDA, ThoiGian) DIADIEM_PHG (MaPHG, DIADIEM) THANNHAN (MaNV, TenTN, Phai, NGSinh,QuanHe) 13 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU TẠO BẢNG, VIEW – VÍ DỤ  Ví dụ: câu lệnh để tạo một table nhân viên CREATE TABLE NHANVIEN ( MANV varchar2(10) NOT NULL, HONV varchar2(50) NOT NULL, TENLOT varchar2(50) NOT NULL, TENNV varchar2(50) NOT NULL, NGAYSINH date, PHAI varchar2(3) NOTNULL, DIACHI varchar2(100), MA_NQL varchar2(10), PHONG varchar2(10), LUONG number, CONSTRAINT PK_NV PRIMARY KEY (MANV), CONSTRAINT FK_NV_PB FOREIGN KEY (PHONG) REFERENCES PHONGBAN (MAPHG) ) 14 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU SỬA CẤU TRÚC BẢNG - ALTER  Thêm thuộc tính ALTER TABLE tên_table ADD tên_cột kiểu_dữ_liệu › Ví dụ: thêm cột Ghi_chú vào table nhân viên ALTER TABLE NHANVIEN ADD GHI_CHU varchar2(20)  Sửa kiểu dữ liệu thuộc tính ALTER TABLE tên_table ALTER COLUMN tên_cột kiểu_dữ_liệu_mới › Ví dụ: sửa kiểu dữ liệu của cột Ghi chú ALTER TABLE NHANVIEN ALTER COLUMN GHI_CHU varchar2(30) 15 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU SỬA CẤU TRÚC BẢNG - ALTER  Xóa thuộc tính ALTER TABLE tên_bảng DROP COLUMN tên_cột › Ví dụ: xóa cột Ghi_chú từ bảng nhân viên ALTER TABLE NHANVIEN DROP COLUMN GHI_CHU  Thêm ràng buộc toàn vẹn ALTER TABLE ADD CONSTRAINT UNIQUE tên_cột PRIMARY KEY (têncột1,2,.) FOREIGN KEY (tên_cột) REFERENCES tên_bảng (cột_là_khóa_chính) CHECK (tên_cột điều_kiện) 16 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU SỬA CẤU TRÚC BẢNG - ALTER  Ví dụ › ALTER TABLE NHANVIEN ADD CONSTRAINT PK_NV PRIMARY KEY (MANV) › ALTER TABLE NHANVIEN ADD CONSTRAINT FK_NV_PB FOREIGN KEY (PHONG) REFERENCES PHONGBAN(MAPHG) › ALTER TABLE NHANVIEN ADD CONSTRAINT CHK CHECK ( PHAI IN („Nam') OR („Nu‟)) › ALTER TABLE NHANKHAU ADD CONSTRAINT UQ_NK UNIQUE (CMND) 17 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU SỬA CẤU TRÚC BẢNG - ALTER  Xóa ràng buộc toàn vẹn ALTER TABLE tên_bảng DROP CONSTRAINT tên_ràng_buộc › Ví dụ ALTER TABLE NHANVIEN DROP CONSTRAINT FK_NV_PB 18 NGÔN NGỮ ĐỊNH NGHĨA DỮ LIỆU XÓA BẢNG, VIEW - DROP  Cú pháp xóa table DROP TABLE tên_table • Ví dụ: xóa bảng (table) nhân viên DROP TABLE NHANVIEN  Cú pháp xóa view DROP VIEW tên_view • Ví dụ: xóa khung nhìn (view) TrucThuoc DROP VIEW TrucThuoc 19 NGÔN NGỮ THAO TÁC DỮ LIỆU THÊM DỮ LIỆU VÀO BẢNG - INSERT  Cú pháp C1: INSERT INTO tên_bảng VALUES (giá_trị_1, giá_trị_2,…, giá_trị_n) C2: INSERT INTO tên_bảng (cột1, cột2) VALUES (giá_trị_1, giá_trị_2) › Ví dụ INSERT INTO NHANVIEN VALUES („001‟, „Vuong‟, „Ngoc‟, „Quyen‟, To_Date(‟01/01/1977‟,’dd-mm-yyyy’), ‟450 Trung Vuong, Ha Hoi‟, „QL‟) 20 NGÔN NGỮ THAO TÁC DỮ LIỆU SỬA DỮ LIỆU CỦA BẢNG - UPDATE  Cú pháp UPDATE tên_bảng SET cột_1 = giá_trị_1, cột_2 = giá_trị_2 [WHERE điều_kiện] › Ví dụ: Sửa họ nhân viên có mã số „001‟ thành „Nguyen‟ UPDATE NHANVIEN SET HONV = „Nguyen‟ WHERE MANV=„001‟ 21 NGÔN NGỮ THAO TÁC DỮ LIỆU SỬA DỮ LIỆU CỦA BẢNG - UPDATE › Ví dụ: Sửa họ tên của nhân viên có mã số „001‟ thành „Nguyen Thanh Tung‟ và ngày sinh mới là 1/1/1978 UPDATE NHANVIEN SET HONV = „Nguyen‟, TENLOT = „Thanh‟, TENNV = Tung‟, NGAYSINH=To_date(‘1/1/1978’, ‘dd-mm-yyyy’) WHERE MANV=„001‟ 22 NGÔN NGỮ THAO TÁC DỮ LIỆU XÓA DỮ LIỆU CỦA BẢNG - DELETE  Cú pháp DELETE FROM tên_bảng [WHERE điều_kiện] › Ví dụ: xóa nhân viên có mã số „001‟ DELETE FROM NHANVIEN WHERE MANV=„001‟ › Ví dụ: xóa toàn bộ nhân viên DELETE FROM NHANVIEN 23 NGÔN NGỮ THAO TÁC DỮ LIỆU TRUY VẤN DỮ LIỆU CỦA BẢNG - SELECT  Câu truy vấn tổng quát SELECT [DISTINCT] tên_cột | hàm FROM bảng [WHERE điều_kiện] [GROUP BY cột] [HAVING điều_kiện] [ORDER BY cột ASC | DESC] 24 NGÔN NGỮ THAO TÁC DỮ LIỆU TRUY VẤN DỮ LIỆU CỦA BẢNG - SELECT  Toán tử so sánh: =, >, =,  Toán tử logic: AND, OR, NOT  Phép toán: +, - ,* , /  Các toán tử so sánh khác › BETWEEN - định nghĩa một đoạn giá trị liên tục › IS NULL - kiểm tra giá trị thuộc tính có null hay không › LIKE – kiểm tra chuỗi ký tự tương tự › IN – kiểm tra giá trị thuộc tính có thuộc tập hợp các giá trị đã định nghĩa hay không › EXISTS – mang giá trị TRUE nếu mệnh đề so sánh trả về ít nhất một bộ (record), FALSE nếu ngược lại 25 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Toán tử BETWEEN › Ví dụ: Tìm nhân viên sinh vào khoảng 1965 và 1977. SELECT * FROM NHANVIEN WHERE To_number(To_char(NGAYSINH,‟yyyy‟)) BETWEEN 1965 AND 1977 › Ví dụ: Tìm nhân viên có lương không nằm trong khoảng 100000 đến 300000 SELECT n.MANV, n.TENNV, p.TENPHG FROM NHANVIEN n, PHONGBAN p WHERE n.PHONG=p.MAPHG AND n.LUONG NOT BETWEEN 100000 AND 300000 26 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Toán tử IS NULL › Ví dụ: tìm những nhân viên có người quản lý SELECT * FROM NHANVIEN WHERE MA_NQL IS NOT NULL › Ví dụ: tìm những học viên chưa đóng tiền học phí SELECT h.MAHV,h.HOTEN,h.DIACHI FROM HOCVIEN h, BIENLAI b WHERE h.MAHV=b.MAHV AND b.TIENNOP IS NULL 27 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Toán tử LIKE › So sánh chuỗi tương đối › Cú pháp: s LIKE p, p có thể chứa % hoặc _ › % : thay thế một chuỗi ký tự bất kỳ › _ : thay thế một ký tự bất kỳ › Ví dụ: SELECT * FROM NHANVIEN WHERE HONV LIKE „Nguyen%‟ 28 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Toán tử IN › Ví dụ a. SELECT * FROM NHANVIEN WHERE PHONG IN („NC‟,‟QL‟,‟DH‟) b. SELECT MANV, TENNV, DIACHI FROM NHANVIEN WHERE MANV NOT IN (SELECT MANV FROM PHANCONG) 29 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Toán tử EXISTS › Ví dụ: tìm nhân viên không làm đề án nào SELECT * FROM NHANVIEN n WHERE NOT EXISTS (SELECT * FROM PHANCONG n.MANV=p.MANV AND) › Ví dụ: tìm nhân viên làm việc cho tất cả các đề án SELECT * FROM NHANVIEN n WHERE NOT EXISTS (SELECT * FROM DEAN d WHERE NOT EXISTS (SELECT * FROM PHANCONG p WHERE n.MANV=p.MANV AND d.MADA=p.MADA)) 30 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY › Chia các dòng thành các nhóm nhỏ dựa trên tập thuộc tính chia nhóm. › Tất cả các thành viên của nhóm đều thỏa các thuộc tính này. › Thực hiện các phép toán trên nhóm như: Count (thực hiện phép đếm), Sum (tính tổng), Min(lấy giá trị nhỏ nhất), Max(lấy giá trị lớn nhất), AVG (lấy giá trị trung bình). 31 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY 32 n h ó m Các thuộc tính GROUP BY: Q a a b b c c c c c d d d Chia các dòng thành các nhóm dựa trên tập thuộc tính chia nhóm Q S Q Count(S) a b c d 2 2 5 3 10 2 9 5 10 8 6 4 10 16 Quan hệ NV 18 50 Câu SQL: Select Q, count(S) From NV Group by Q Tương tự cho các hàm SUM, MIN, MAX, AVG TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY Ví dụ:trong từng phòng ban, tìm nhân viên có thời gian làm việc max,min,avg,sum SELECT n.MANV, n.TENNV, n.PHONG, MIN(p.THOIGIAN) thap_nhat, MAX (p.THOIGIAN) cao_nhat, AVG(p.THOIGIAN) trung_binh, SUM (p.THOIGIAN) tong_so_gio FROM NHANVIEN n, PHANCONG p WHERE n.MANV=p.MANV GROUP BY n.MANV, n.TENNV, n.PHONG 33 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY có điều kiện HAVING › Lọc kết quả theo điều kiện, sau khi đã gom nhóm › Điều kiện của HAVING là điều kiện các hàm tính toán trên nhóm (Count, Sum, Min, Max, AVG) và các thuộc tính trong danh sách GROUP BY. 34 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY có điều kiện HAVING › Ví dụ: cho biết tên từng phòng ban và tổng số nhân viên, mức lương trung bình của phòng trên 2000000 SELECT p.TENPHG, COUNT(*) so_luong_nv, AVG(n.LUONG) luong_tb FROM NHANVIEN n, PHONGBAN p WHERE n.PHONG = p.MAPHG GROUP BY p.TENPHG HAVING AVG(n.LUONG) > 2000000 35 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Mệnh đề GROUP BY có điều kiện HAVING  Ví dụ: Liệt kê nhân viên có số giờ làm việc nhiều nhất trong công ty. SELECT n.MANV, n.TENNV, SUM (p.THOIGIAN) FROM NHANVIEN n, PHANCONG p WHERE n.MANV = p.MANV GROUP BY n.MANV, n.HOTEN HAVING SUM (p.THOIGIAN) >= ALL (SELECT SUM(THOIGIAN) FROM PHANCONG GROUP BY (MANV) 36 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Các hàm SQL cơ bản › COUNT: Đếm số bộ dữ liệu của thuộc tính › MIN: Tính giá trị nhỏ nhất › MAX: Tính giá trị lớn nhất › AVG: Tính giá trị trung bình › SUM: Tính tổng giá trị các bộ dữ liệu › …. 37 TRUY VẤN DỮ LIỆU CỦA BẢNG – VÍ DỤ Các hàm SQL cơ bản  Ví dụ: tìm tổng lương, lương lớn nhất, lương ít nhất và lương trung bình của các nhân viên SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM NHANVIEN ;  Ví dụ: tìm tổng lương, lương lớn nhất, lương ít nhất và lương trung bình của các nhân viên phòng “Nghiên cứu” SELECT SUM(LUONG), MAX(LUONG), MIN(LUONG), AVG(LUONG) FROM NHANVIEN , PHONGBAN WHERE MAPHG=PHONG AND TENPHG='Nghien cuu';  Ví dụ: cho biết số lượng nhân viên SELECT COUNT(*) FROM NHANVIEN; 38 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Giới thiệu System Privileges và Roles  Một User muốn cấp quyền (role) cần phải có 2 quyền hệ thống thích hợp là CONNECT đến cơ sở dữ liệu và CREATE Object (Default).  System Privileges: có hơn 8 quyền có thể gán cho User và Role, 8 quyền bao gồm: SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, ALL.  Role: là tên của một tập hợp các quyền hệ thống nhằm quản lý các quyền cho các ứng dụng cơ sở dữ liệu hoặc nhóm người dùng (User Group).  Một User có thể truy xuất đến nhiều Role và ngược lại.  Có vài Role được định nghĩa trước như DBA chứa tất cả các quyền của hệ thống. 39 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Tạo quyền - Roles  Cú pháp: CREATE ROLE “” NOT IDENTIFIED | IDENTIFIED BY “” Trong đó: - NOT IDENTIFIED: không đặt password cho role. - IDENTIFIED BY password: user phải nhập password để làm cho role có hiệu lực. › Ví dụ: tạo một role “QuanTriSV” với password là 987654 CREATE ROLE “QuanTriSV” IDENTIFIED BY “987654” 40 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU - Thiết lập/Thay đổi/Xóa mật khẩu cho Role - Xóa Role  Thiết lập/thay đổi/bỏ mật khẩu cho quyền: Cú pháp: ALTER ROLE “” NOT IDENTIFIED | IDENTIFIED BY “” › Ví dụ: thay đổi password mới cho role “QuanTriSV” là 123456789 ALTER ROLE “QuanTriSV” IDENTIFIED BY “123456789”  Xóa quyền: Cú pháp: DROP ROLE “” Ví dụ: xóa quyền “QuanTriSV” DROP ROLE “QuanTriSV” 41 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Tạo người dủng - User  Cú pháp CREATE USER “” NOT IDENTIFIED | IDENTIFIED BY “” Trong đó: - NOT IDENTIFIED: không đặt password cho role. - IDENTIFIED BY password: user phải nhập password mới có thể đăng nhập hệ thống. › Ví dụ: tạo một user “Phuong” với password là 987654 CREATE USER “Phuong” IDENTIFIED BY “987654” 42 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU - Thiết lập/Thay đổi/Xóa mật khẩu cho User - Xóa User  Thiết lập/thay đổi/bỏ mật khẩu cho người dùng Cú pháp: ALTER USER “” NOT IDENTIFIED | IDENTIFIED BY “” › Ví dụ: thay đổi password mới cho user “Phuong” là 123456789 ALTER USER “Phuong” IDENTIFIED BY “123456789”  Xóa người dùng Cú pháp: DROP USER “” Ví dụ: xóa người dùng “Phuong” DROP USER “Phuong” 43 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Cấp phát quyền hạn (Privileges) cho User/Role  Cú pháp cấp phát quyền cho người dùng GRANT privil1, privil2,…/ALL ON TO User1, User2,… [WITH GRANT OPTION]  Cú pháp cấp phát quyền cho Role GRANT privil1, privil2,…/ALL ON TO Role1, Role2,… [WITH GRANT OPTION] Trong đó: - : có thể là tên của một Table, View, Sequence, Synonym, Procedure, Function, Package. - privil1, privil1,..là 1 trong 8 quyền hệ thống nêu trên để cấp quyền trên table hay view. - WITH GRANT OPTION: cho phép User/Roles được phép gán quyền đó tiếp choUser/Roles khác 44 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Cấp phát quyền hạn (Privileges) cho User/Role  Ban quyền truy xuất đến tất cả User, Role bằng lệnh GRANT privil1, privil2,…/ALL ON TO PUBLIC  Ví dụ 1: cấp phát quyền cho user “Phuong” GRANT INSERT, UPDATE ON TênUserTạoTableNhanVien.NHANVIEN TO “Phuong”  Ví dụ 2: cấp phát quyền cho role “QuanTriSV” GRANT ALL ON TênUserTạoTableNhanVien.NHANVIEN TO “QuanTriSV” WITH GRANT ADMIN 45 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Cấp phát quyền hạn (Privileges) cho User/Role  Gán quyền Role cho User (User được cấp phát quyền Role) GRANT tên-Role TO tên-User [WITH GRANT OPTION]  Ví dụ 1: cấp phát role “QuanTriSV” cho user “Phuong” GRANT “QuanTriSV” TO “Phuong”  Ví dụ 2: cấp phát role “QuanTriSV” cho user “Phuong” và cho phép user “Phuong” gán quyền “QuanTriSV” đến những user khác. GRANT “QuanTriSV” TO “Phuong” WITH GRANT ADMIN 46 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Thu hồi quyền hạn (Privileges) cho User/Role  Cú pháp rút lại (hủy bỏ) các quyền đã cấp phát REVOKE privil1, privil2,…/Role1, Role2,… ON / FROM User1, User2,…/Role1, Role2,…  Ví dụ REVOKE UPDATE, DELETE ON NHANVIEN FROM “Phuong” REVOKE ALL ON NHANVIEN FROM “QuanTriSV” 47 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Các lệnh giao tác: Commit,RollBack,SavePoint…  GIAO TÁC: một Transaction là một giao tác trên CSDL bao gồm chuỗi các thay đổi (hành động) trên một hay nhiều table. Điều khiển Transaction bằng các lệnh sau.  Lệnh COMMIT (hoàn tất giao tác) Cú pháp: COMMIT  Lệnh ROLLBACK (phục hồi ngược lại chuỗi hành động đã thực hiện trước đó). Cú pháp: ROLLBACK [TO SAVEPOINT name]  Lệnh SavePoint Cú pháp: SAVEPOINT tên-SavePoint  Chế độ AutoCommit (AUTOCOMMIT ON/OFF ) 48 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Các lệnh giao tác – Ví dụ  Ví dụ: minh họa cách dùng các lệnh SavePoint, RollBack, Commit INSERT INTO DEPT VALUES (50, „TESTING‟, „LYON‟); SAVEPOINT insert_point1; UPDATE DEPT SET DNAME=‟MARKETING‟; ROLLBACK TO insert_point1; /*Lệnh UPDATE bị thải hồi*/ UPDATE DEPT SET DNAME=‟MARKETING‟ WHERE DNAME=‟SALES‟; /*Lệnh UPDATE được duyệt lại*/ COMMIT; 49 NGÔN NGỮ ĐIỀU KHIỂN DỮ LIỆU Các lệnh giao tác – Ví dụ  Ví dụ: minh họa cách dùng các lệnh SavePoint, RollBack, Commit INSERT INTO DEPT VALUES (50, „TESTING‟, „LYON‟); SAVEPOINT insert_point1; UPDATE DEPT SET DNAME=‟MARKETING‟; ROLLBACK TO insert_point1; /*Lệnh UPDATE bị thải hồi*/ UPDATE DEPT SET DNAME=‟MARKETING‟ WHERE DNAME=‟SALES‟; /*Lệnh UPDATE được duyệt lại*/ COMMIT; 50 CÁC ĐỐI TƯỢNG KHÁC ĐỐI TƯỢNG SYSNONYM 51  Synonym: Là tên đặt cho một đối tượng cụ thể nào đó (bí danh). Thường dùng synonym để tạo ra những đối tượng dùng chung.  Ví dụ: Một người dùng muốn sử dụng một bảng được sở hữu bởi người dùng khác thay vì gọi “tên_người_dùng.tên_bảng” thì user đó tạo ra một synonym cho bảng đó với một tên dễ nhớ nào đó, tên thật của đối tượng được che dấu đi.  Các thao tác trên Synonym: tạo Synonym, xóa Synonym. CÁC ĐỐI TƯỢNG KHÁC ĐỐI TƯỢNG SYSNONYM 52  Tạo Synonym: Cú pháp: CREATE SYNONYM tênSynonym FOR tênUser.tênTable|tênView Ví dụ: CREATE SYNONYM nv FOR phuong.NHANVIEN;  Xóa Synonym: Cú pháp: DROP SYNONYM tên-synonym; Ví dụ: DROP SYNONYM nv; CÁC ĐỐI TƯỢNG KHÁC ĐỐI TƯỢNG INDEX 53  Index (tạo chỉ mục): sử dụng Oracle Index nhằm - Tăng tốc độ xây dựng lại các dòng theo một khóa đặc biệt. - Bảo đảm giá trị duy nhất trong cột, thường là giá trị primary key.  Tạo Index Cú pháp: CREATE [UNIQUE] INDEX index_name ON table (column1 [, ] , …) Ví dụ: CREATE UNIQUE IN