Bài giảng Hệ quản trị CSDL Oracle - Phần 3: Ngôn ngữ SQL

 
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 INDEX i_cmnd ON 
NHAN_KHAU (SO_CMND); 
CÁC ĐỐI TƯỢNG KHÁC 
ĐỐI TƯỢNG INDEX 
54 
 Phân loại index: 
- UNIQUE: Bảo đảm giá trị trong các cột là duy nhất. 
- NON UNIQUE: kết quả truy vấn có thể nhanh nhất. 
- SINGLE COLUMN: Chỉ một cột tồn tại index. 
- CONCATENATED: Trên 16 cột chỉ trong index. 
 Xóa index 
Cú pháp: DROP INDEX index_name; 
Ví dụ: DROP INDEX i_cmnd; 
CÁC ĐỐI TƯỢNG KHÁC 
ĐỐI TƯỢNG SEQUENCE – TẠO 
55 
 Sequence (giá trị được tạo tự động) 
 Sequence là đối tượng tạo ra một dãy số liên tiếp một cách tự 
động, thường hay sử dụng trong câu lệnh INSERT để nhập dữ liệu 
cho bảng (ví dụ mã khách hàng tăng tự động). 
 Tạo Sequence 
 Cú pháp: 
 CREATE SEQUENCE sequence_name 
 [INCREMENT BY n] 
 [START WITH n] 
 [MAXVALUE n | NOMAXVALUE] 
 [MINVALUE n | NOMINVALUE] 
 [CYCLE | NOCYCLE] 
 [CACHE n | NOCACHE] 
 [ORDER | NOORDER] 
CÁC ĐỐI TƯỢNG KHÁC 
ĐỐI TƯỢNG SEQUENCE – VÍ DỤ 
56 
 Ví dụ 
 CREATE SEQUENCE s_nv 
 MINVALUE 1 
 MAXVALUE 9999999 
 INCREMENT BY 1 
 START WITH 100 
 NOCACHE 
 NOORDER 
 NOCYCLE; 
 Sử dụng Sequence giá trị hiện hành và kế tiếp của dãy sequence 
được lưu trong hai cột: 
 - Giá trị hiện hành của sequence: tên_sequence.CURRVAL 
 - Giá trị kế tiếp của sequence: tên_sequence.NEXTVAL 
CÁC ĐỐI TƯỢNG KHÁC 
ĐỐI TƯỢNG SEQUENCE 
57 
 Hai cột CURRVAL, NEXTVAL trong Oracle được gọi là 
pseudo column (cột ảo), người dùng chỉ được truy xuất, không 
được cập nhật dữ liệu trong các cột này. 
 Oracle có các cột ảo như: ROWID (mã dòng), ROWNUM (số 
thứ tự dòng), SYSDATE (ngày hiện hành của hệ thống),… 
 Ví dụ: 
 Khởi tạo giá trị sequence s_nv bằng lệnh: 
 SELECT s_nv.nextval from DUAL; (bảng DUAL là tạm trong 
Oracle) 
 Truy vấn giá trị hiện tại của sequence s_nv: 
 SELECT s_nv.currval from DUAL; 
 Sử dụng sequence trong một câu insert: 
 INSERT INTO NHANVIEN (MA_NV, HO_TEN) VALUES 
(s_nv.nextval, „Nguyen van A‟); 
 /* s_nv là sequence đã tạo trước. */ 
CÁC ĐỐI TƯỢNG KHÁC 
ĐỐI TƯỢNG SEQUENCE – SỬA, XÓA 
58 
 Sửa Sequence (tạo lại các thông số) 
 ALTER SEQUENCE tên-sequence 
 [INCREMENT BY n] 
 [START WITH n] 
 [MAXVALUE n | NOMAXVALUE] 
 [MINVALUE n | NOMINVALUE] 
 [CYCLE | NOCYCLE] 
 [CACHE n | NOCACHE] 
 [ORDER | NOORDER] 
 Xóa Sequence 
 Cú pháp DROP SEQUENCE tên-sequence 
 Ví dụ: DROP SEQUENCE s_nv