Professional Documents
Culture Documents
Ngon Ngu Lap Trinh SQL 2
Ngon Ngu Lap Trinh SQL 2
6 BIẾN RUNTIME...............................................................................................................27
Trang 2 / 47
8 CÁC LỆNH THAO TÁC DỮ LIỆU................................................................................39
9 SEQUENCE VÀ INDEX..................................................................................................42
9.1 SEQUENCE......................................................................................................................................42
9.1.1 TẠO SEQUENCE...........................................................................................................................................42
9.1.2 THAY ĐỔI VÀ HUỶ SEQUENCE...............................................................................................................43
9.2 INDEX 43
9.2.1 TẠO INDEX....................................................................................................................................................43
9.2.2 SỬ DỤNG INDEX..........................................................................................................................................43
10 VIEWS................................................................................................................................45
10.1 VIEWS 45
10.1.1 TẠO VIEW.....................................................................................................................................................45
10.1.2 XÓA CÁC VIEW............................................................................................................................................45
11.2 ROLE 48
11.3 SYNONYM.......................................................................................................................................48
Trang 3 / 47
1 GIỚI THIỆU CHUNG
Trang 4 / 47
ban có trong bảng phòng ban. Ta thể hiển thị column này thông qua
tên
column và có thể kèm theo một vài thông tin khác về column như
kiểu dữ liệu, độ dài của dữ liệu.
Giao của column và row. Field chính là nơi chứa dữ liệu. Nếu không
Field có dữ liệu trong field ta nói field có gia trị là NULL.
Là một column hoặc một tập các column xác định tính duy nhất của
các rows ở trong bảng. Ví dụ DEPTNO là Primary Key của bảng
DEPT
vì nó được dùng để xác định duy nhất một phòng ban trong bảng
Primary Key DEPT mà đại diện là một row dữ liệu.
Là một column hoặc một tập các columns có tham chiếu tới chính
bảng đó hoặc một bảng khác.
Foreign Key Foreign Key xác định mối quan hệ giữa các bảng.
Là các ràng buộc đối với dữ liệu trong các bảng thuộc database. Ví
Constraints dụ: Foreign Key, Primary Key...
Table Cấu trúc lưu trữ cơ bản nhất trong CSDL quan hệ (RDBMS), gồm
row và column
View Là cấu trúc logic hiển thị dữ liệu từ 1 hoặc nhiều bảng
Sequence Sinh giá trị cho các primary key
Index Tăng tính thực thi cho câu lệnh truy vấn
Synonym Tên tương đương của đối tượng
Tập hợp các câu lệnh thực hiện được viết bởi ngôn ngữ SQL và
Program unit PL/SQL, bao gồm Procedure, function, package...
Trang 5 / 47
Lệnh định nghĩa cấu trúc dữ liệu (DDL – create, alter, replace, drop): tạo, thay thế,
sửa đổi, xóa.
Lệnh phân quyền truy cập (grant, revoke)
Lệnh quản lý giao tác: save point, commit, rollback.
Trang 6 / 47
2 LỆNH TRUY VẤN CƠ BẢN
Trang 7 / 47
Biểu thức toán học
Trong mệnh đề SELECT biểu thức toán học có thể các giá trị (column hoặc hàng số), các
toán tử, các hàm. Các toán tử được dùng là (+), (-), (*), (/). Độ ưu tiên của các toán tử
giống trong phần số học.
Ví dụ:
SELECT ename, sal *12, comm FROM emp;
SELECT ename, (sal+250)*12 FROM emp;
10
30
10
20
14 rows selected.
Trang 8 / 47
SQL> SELECT DISTINCT deoptno FROM dept;
DEPTNO
10
30
20
3 rows selected.
Trong các hàm làm việc với nhóm các cột (group function): Hầu hết các hàm làm việc trên
nhóm bỏ qua trị null, ví dụ như khi sử dụng hàm AVG để tính trung bình cho một cột có
các giá trị 1000, NULL, NULL, NULL, 2000. Khi đó trung bình được tính là
(1000+2000)/2=1500, như vậy trị null bị bỏ qua chứ không phải xem là trị 0.
Nếu muốn chọn các nhân viên có comm là NULL thì phải dùng toán tử IS NULL
SELECT * FROM emp WHERE comm IS NULL;
Trang 9 / 47
2.2 SQL*PLUS, CÔNG CỤ TƯƠNG TÁC LỆNH SQL VỚI
DATABASE
Câu lệnh dựa trên bộ ký tự chuẩn ASCII Tuỳ thuộc vào từng phiên bản của Oracle
Câu lệnh được nạp vào bộ nhớ đệm trên Câu lệnh được tải trực tiếp không thông qua
một hoặc nhiều dòng bộ đệm
Câu lệnh không được viết tắt Câu lệnh có thể viết tắt
Trang 10 / 47
Có sử dụng ký tự kết thúc lệnh khi thực Không đòi hỏi phải có ký tự kết thúc lệnh
hiện
Sử dụng các hàm trong việc định dạng Sử dụng các lệnh định dạng dữ liệu của chính
dữ liệu SQL*Plus
Trang 11 / 47
Lệnh soạn thảo
Tên lệnh Diễn giải
A[PPEND] text Đưa thêm đoạn text vào dòng hiện tại
C[HANGE] Chuyển đoạn text cũ thành đoạn text mới trong dòng hiện tại
/old/new
C[HANGE] /text/ Xoá đoạn text trong dòng hiện tại
CL[EAR] Xoá tất cả các dòng trong SQL buffer
BUFF[ER]
DEL Xoá dòng hiện tại
DEL n Xoá dòng n
DEL m n Xoá dòng từ m đến n
I[NPUT] Thêm một số dòng nhất định
I[NPUT] text Thêm dòng có chứa text
L[IST] Liệt kê toàn bộ các dòng trong SQL buffer
Trang 12 / 47
3 TRUY VẤN DỮ LIỆU CÓ ĐIỀU KIỆN
Mệnh đề WHERE dùng để đặt điều kiện cho toàn bộ câu lệnh truy vấn. Trong mệnh đề
WHERE có thể có các thành phần:
Trang 13 / 47
Tên column
Toán tử so sánh
Tên column, hằng số hoặc danh sách các giá trị
Ví dụ:
SELECT DEPTNO, JOB, ENAME, SAL FROM EMP
WHERE SAL BETWEEN 1000 AND 2000 ;
Truy vấn dữ liệu với nhiều điều kiện
Mệnh đề WHERE cho phép ghép được nhiều điều kiện thông qua các toán tử logic
AND/OR. Toán tử AND yêu cầu dữ liệu phải thoả mãn cả 2 điều kiện. Toán tử OR cho
phép dữ liệu thoả mãn 1 trong 2 điều kiện.
Ví dụ:
SELECT DEPTNO, JOB, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000
AND JOB = ‘MANAGER’;
SELECT DEPTNO, JOB, ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000
OR JOB = ‘MANAGER’;
SELECT DEPTNO, JOB, EMPNO, ENAME, SAL FROM EMP WHERE SAL > 1500
AND JOB = ‘MANAGER’ OR JOB =’SALESMAN’;
SELECT DEPTNO, JOB, EMPNO, ENAME, SAL FROM EMP WHERE SAL > 1500
AND (JOB = ‘MANAGER’ OR JOB =’SALESMAN’);
Toán tử SQL:
Trang 14 / 47
Toán tử logic:
Cấp độ ưu tiên khi thực hiện đối với các loại toán tử :
IN (danh sách)
Chọn nhân viên có lương bằng một trong 2 giá trị 1400 hoặc 3000
SELECT * FROM emp WHERE sal IN (1400, 3000);
Tìm tên phòng ban nếu phòng đó có nhân viên làm việc.
SELECT dname FROM dept WHERE EXISTS
(SELECT * FROM emp WHERE dept.deptno = emp.deptno);
x [NOT] LIKE y
Tìm nhân viên có tên bắt đầu bằng chuỗi SMITH
SELECT * FROM emp WHERE
ename LIKE 'SMITH_';
Vì ký hiệu "_" dùng để đại diện cho một ký tự bất kỳ nên nếu không có mệnh đề
ESCAPE, câu lệnh trên sẽ tìm tất cả các nhân viên tên AAB, ABB, ACB, v.v...
Nếu muốn ký hiệu "_" mang ý nghĩa nguyên thủy, tức là không còn đại diện cho ký tự bất
kỳ nữa, ta đặt dấu "\" trước ký hiệu. Đồng thời khai báo thêm mệnh đề ESCAPE "\"
Ta cũng có thể dùng một ký tự bất kỳ thay cho "\". Chẳng hạn mệnh đề sau có cùng kết
quả với mệnh đề trên
SELECT ename FROM emp WHERE ename LIKE '%A^_B%'; ESCAPE '^';
Ta gọi các ký tự như "\" hay "^" nói trên là các ký tự ESCAPE.
Trang 15 / 47
IS [NOT] NULL
Ví dụ:
SELECT * FROM emp WHERE comm IS NULL ;
Mệnh đề ORDER BY dùng để sắp xếp số liệu được hiển thị và phải đặt ở vị trí sau cùng
của câu lệnh truy vấn.
Ví dụ:
SELECT ENAME, JOB, SAL*12, DEPTNO FROM EMP ORDER BY ENAME;
Mệnh để ORDER BY mặc định sắp xếp theo thứ tự tăng dần ASC[ENDING]
Số thấp trước
Ngày nhỏ trước
Ký tự theo bảng chữ cái
Để sắp xếp theo thứ tự ngược lại (giảm dần) đặt từ khoá DESC[ENDING] sau column cần
sắp thứ tự.
Vi dụ:
SELECT ENAME, JOB, HIREDATE FROM EMP ORDER BY HIREDATE DESC ;
Trang 16 / 47
Trang 17 / 47
4 CÁC HÀM SQL
Trang 18 / 47
Hình 4 Phân loại hàm SQL
Trang 19 / 47
4.2.3 CÁC HÀM THAO TÁC TRÊN KIỂU DỮ LIỆU THỜI GIAN
Một số hàm kiểu dữ liệu ký tự thông dụng
Trang 20 / 47
4.2.4 CÁC HÀM CHUYỂN ĐỔI KIỂU
Chú ý: Tất cả các hàm trên nhóm mẫu tin đều bỏ qua giá trị NULL trừ hàm COUNT.
Dùng hàm NVL để chuyển đổi và tính giá trị NULL.
Có 2 cách để dùng các các hàm này
Tác động trên toàn bộ các dòng dữ liệu của câu lệnh truy vấn
Tác động trên một nhóm dữ liệu cùng tính chất của câu lệnh truy vấn. Cùng tính chất
được chỉ bởi mệnh đề:
[GROUP BY expr][HAVING condition]
Trang 21 / 47
Mệnh đề GROUP BY sẽ nhóm các dòng dữ liệu có cùng giá trị của expr. Ví dụ: GROUP
BY JOB nghĩa là sẽ nhóm các nghề giống nhau.
Mệnh đề HAVING là đặt điều kiện của nhóm dữ liệu. Mệnh đề này khác mệnh đề
WHERE ở chỗ mệnh đề WHERE đặt điều kiện cho toàn bộ câu lệnh SELECT.
Trang 22 / 47
5 LỆNH TRUY VẤN DỮ LIỆU MỞ RỘNG
Các column trùng tên phải được chỉ rõ column đó nằm ở bảng nảo thông qua tên hoặc qua
alias. Tên trùng này có thể đặt trong các mệnh đề khác như SELECT, ORDER BY..
Ví dụ:
SELECT DEPT.DEPTNO, ENAME,JOB, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO =
DEPT.DEPTNO ORDER BY DEPT.DEPTNO;
Các column trùng tên phải được chỉ rõ column đó nằm ở bảng nào thông qua tên hoặc qua
alias. Tên trùng này có thể đặt trong các mệnh đề khác như SELECT, ORDER BY..
Ví dụ:
SELECT E.ENAME,E.JOB, S.GRADE FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN
S. LOSAL AND S.HISAL;
Chú ý: Điều kiện liên kết đúng là số các bảng - 1 = số các điều kiện liên kết
Trang 23 / 47
Trong mệnh đề WHERE của mối liên kết cộng không được dùng toán tử IN hoặc OR để
nối các điều kiện liên kết khác.
ENAME JOB
BLAKE MANAGER
CLARK MANAGER
JONES MANAGER
Trang 24 / 47
Trong mệnh đề HAVING
Tìm những phòng có mức lương trung bình lớn hơn phòng 30
SELECT DEPTNO, AVG(SAL) FROM EMP HAVING AVG(SAL) > (SELECT AVG(SAL) FROM
EMP WHERE DEPTNO =30) GROUP BY DEPTNO;
DEPTNO AVG(SAL)
10 2916.66667
20 2175
Trang 25 / 47
6 BIẾN RUNTIME
Ví du 2:
Select ename, deptno, job From emp Where deptno = &&depno_please;
Để liệt kê các biến đã khai báo dùng lệnh DEFINE mà không chỉ biến, ví dụ
DEFINE
DEFINE CONDITION = 'SAL > 1000'
Ví dụ:
DEFINE REM=’SAL*12+NVL(COMM,0)’
Từ khoá hide cho phép che chuỗi nhập liệu, hay dùng khi nhập password.
ACCEPT password CHAR PROMPT 'Enter password: ' HIDE Password : ***
Trang 26 / 47
7 TABLE VÀ CÁC LỆNH SQL VỀ TABLE
Ví dụ 1:
CREATE TABLE EMP
EMPNO NUMBER NOT NULL CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10) CONSTRAINT NN_ENAME NOT NULL CONSTRAINT UPPER_ENAME
CHECK (ENAME=UPPER(ENAME)), JOB VARCHAR2(9), MGR NUMBER CONSTRAINT
FK_MGR REFERENCES SCOTT.EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE, SAL
NUMBER(10,2) CONSTRAINT CK_SAL CHECK(SAL>500), COMM NUMBER(9,0) DEFAULT
NULL, DEPTNO NUMBER(2) CONSTRAINT NN_DEPTNO NOT NULL CONSTRAINT
FK_DEPTNO REFERENCES SCOTT.DEPT(DEPTNO)) ;
Ví du 2:
CREATE TABLE SALGRADE1 (GRADE NUMBER CONSTRAINT PK_SALGRADE PRIMARY KEY,
LOSAL NUMBER, HISAL NUMBER) TABLESPACE USER ;
Ví dụ 3:
CREATE TABLE DEPT10 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE
DEPTNO =10;
Ví dụ 4:
CREATE TABLE EMP_SAL (NAME, SALARY,GRADE)AS SELECT ENAME, SAL, GRADE
FROM EMP, SALGARDE WHERE EMP.SAL BETWEEN LOSAL AND HISAL ;
Để tạo một table mới, chúng ta cần phải chuẩn bị một số thông tin sau:
Table phải được chuẩn hóa.
Những column mà cho phép null nên định nghĩa sau để tiết kiệm nơi lưu trữ.
Gộp các table lại nếu có thể.
Trang 27 / 47
Có thể chỉ định tablespace cho table
Có thể ước lượng kích thước table, và các thông số cho storage.
2. Tính toán khoảng đĩa trống để chứa dữ liệu của data block. Tính theo công thức sau:
Khoảng đĩa trống để chứa data =(block size -total block header) -
(block size -(fixed header+ variable transaction
header))*(pctree/100)
3. Tính toán khoảng đĩa trống kết hợp bằng giá trị của mỗi row.
Trang 28 / 47
Tên không được trùng với các từ đã dùng bởi Oracle (xemphu lục 1) Tên không
được cách khoảng trống
Tên có thể đặt trong cặp dấu nháy kép, khi đó tên có thể bao gồm các ký tự bất kỳ,
có thể bao gồm khoảng trống, có thể dùng các từ khóa của Oracle, phân biệt chữ
hoa chữ thường.
Tên phải duy nhất trong "không gian tên" nhất định. Các object thuộc cùng không
gian tên phải có tên khác nhau.
Các bí danh của cột, bí danh bảng, tên người sử dụng, mật khẩu mặc dù không phải là các
object hoặc các thành phần con của object nhưng cũng phải được đặt tên theo các quy tắc
trên, ngoại trừ
Bí danh cột, bí danh bảng chỉ tồn tại khi thực hiện các lệnh SQL và không được lưu trữ
trong CSDL, do vậy không áp dụng quy tắc 9 về không gian tên.
Mật khẩu không thuộc về không gian tên nào và do đó cũng không áp dụng quy tắc 9. Nên
đặt tên theo một quy tắc đặt tên thống nhất
Trong đó:
object Tên object
schema Schema chứa object
part Thành phần của object
dblink Tên CSDL chứa object
Trang 29 / 47
schema.object
Trang 30 / 47
s Số các chữ số tính từ dấu chấm thập phân về bên phải
(scale), s từ -84 đến 127
NUMBER(p) Số có dấu chấm thập phân cố định với precision bằng p và
scale bằng 0
NUMBER Số với dấu chấm động với precision bằng 38. Nhớ rằng
scale không được áp dụng cho số với dấu chấm động
Trang 31 / 47
Với giá trị kiểu DATE, những thông tin được lưu trữ gồm thế kỷ, năm, tháng, ngày, giờ,
phút, giây. Oracle không cho phép gán giá trị kiểu ngày trực tiếp, để gán giá trị kiểu ngày,
bạn phải dùng TO_DATE để chuyển giá trị kiểu chuỗi ký tự hoặc kiểu số.
Nếu gán một giá trị kiểu ngày mà không chỉ thời gian thì thời gian mặc định là 12 giờ
đêm, Nếu gán giá trị kiểu ngày mà không chỉ ra ngày, thì ngày mặc định là ngày đầu của
tháng. Hàm SYSDATE cho biết ngày và thời gian hệ thống.
Tính toán đối với kiểu ngày
Đối với dữ liệu kiểu ngày, bạn có thể thực hiện các phép toán cộng và trừ.
Ví dụ:
SYSDATE+1 ngày hôm sau
SYSDATE-7 cách đây một tuần
SYSDATE+(10/1440) mười phút sau
Ngày Julian: Là giá trị số cho biết số ngày kể từ ngày 1 tháng giêng năm 4712 trước công
nguyên.
Ví dụ:
SELECT TO_CHAR (TO_DATE('01-01-1992', 'MM-DD-YYYY'), 'J') JULIAN FROM
DUAL;
Kết quả: JULIAN 2448623
Trang 32 / 47
7.3.11 CHUYỂN ĐỔI KIỂU
Chuyển đổi mặc định
Nói chung một biểu thức không thể gồm các giá trị thuộc nhiều kiểu khác nhau, tuy nhiên
Oracle cho phép chuyển đổi giưã các kiểu dữ liệu. Oracle tự động chuyển kiểu của dữ liệu
trong một số trường hợp sau
Khi INSERT hoặc UPDATE gán giá trị cho cột có kiểu khác, Oracle sẽ tự động
chuyển giá trị sang kiểu của cột.
Khi sử dụng các hàm hoặc các toán tử mà các tham số có kiểu không tương thích thì
Oracle sẽ tự động chuyển kiểu.
Khi sử dụng toán tử so sánh mà các giá trị có các kiểu khác nhau, Oracle sẽ tự động
chuyển kiểu.
Ví dụ 1:
SELECT ename FROM emp WHERE hiredate = '12-MAR-1993'
Oracle đã tự động chuyển chuỗi '12-MAR-1993' sang kiểu DATE trong phép
so sánh
Ví dụ 2:
SELECT ename FROM emp WHERE ROWID = '00002514.0001.0001'
Oracle đã tự động chuyển chuỗi '00002514.0001.0001' sang kiểu ROWID
trong phép so sánh
Trang 33 / 47
7.4.2 UNIQUE
Chỉ ra ràng buộc duy nhất, các giá trị của column chỉ trong mệnh đề UNIQUE trong các
row
của table phải có giá trị khác biệt. Giá trị null là cho phép nêu UNIQUE dựa trên một cột.
Ví dụ:
CREATE TABLE DEPT ( DEPTNO NUMBER(2), DNAME CHAR(14), LOC CHAR(13),
CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME, LOC));
7.4.5 CHECK
Ràng buộc kiểm tra giá trị.
Ví dụ:
CREATE TABLE EMP (EMPNO NUMBER NOT NULL CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10) CONSTRAINT NN_ENAME NOT NULL CONSTRAINT UPPER_ENAME
CHECK (ENAME = UPPER(ENAME)), JOB VARCHAR2(9), MGR NUMBER CONSTRAINT
FK_MGR REFERENCES SCOTT.EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE, SAL
NUMBER(10,2) CONSTRAINT CK_SAL CHECK(SAL>500), COMM NUMBER(9,0) DEFAULT
NULL, DEPTNO NUMBER(2) CONSTRAINT NN_DEPTNO NOT NULL CONSTRAINT
FK_DEPTNO REFERENCES SCOTT.DEPT(DEPTNO);
Trang 34 / 47
ALTER TABLE tablename [ADD/MODIFY/DROP options ([column [column
constraint) [ENABLE clause] [DISABLE clause]
Trong đó:
ADD Thêm column hay constraint.
MODIFY Sửa đổi kiểu các column
DROP Bỏ constraint.
ENABLE/DISABLE Che khuất hoặc đưa vào sử dụng các CONSTRAINT mà không
xóa hẳn
Chú ý:
Khi dùng mệnh đề MODIFY không thể chuyển tính chất của COLUMN có nội dung
là NULL chuyển thành NOT NULL;
Không thể đưa thêm một cột NOT NUL nếu table đã có số liệu. Phải thêm cột NULL,
điền đầy số liệu, sau đó chuyển thành NOT NULL.
Không thể chuyển đổi kiểu khác nhau nếu column đã chứa số liệu
Không thể dùng mệnh đề MODIFY để định nghĩa các CONSTRAINT trừ ràng buộc
NULL/NOT NULL. Muốn sửa CONSTRAINT cần xoá chúng sau đó ADD thêm vào.
Ví dụ 1:
ALTER TABLE emp ADD (spouse_name CHAR(10);
Ví dụ 2:
ALTER TABLE emp MODIFY (ename CHAR(25);
Ví dụ 3:
ALTER TABLE emp DROP CONSTRAINT emp_mgr; ALTER TABLE DROP PRIMARY KEY;
Ví dụ 4:
ALTER TABLE dept DISABLE CONSTRAINT dept_prim;
Trang 35 / 47
7.5.3 CHÚ DẪN CHO TABLE
Dùng lệnh COMMENT để chú thích.
Ví dụ:
COMMENT ON TABLE EMP IS ‘ THONG TIN NHAN VIEN’;
COMMENT ON COLUMN EMP.EMPNO IS ‘ MA SO NHAN VIEN’;
Muốn hiển thị toàn bộ thông tin về các table, view, snapshot trong từ điển dữ liệu dùng
lệnh
SELECT * FROM DICTIONARY;
Trang 36 / 47
Hiển thị cấu của USER_OBJECT
DESCRIBE USER_OBJECT;
Hiển thị tất cả các loại đối tượng trong từ điển dữ liệu:
SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS;
Trang 37 / 47
8 CÁC LỆNH THAO TÁC DỮ LIỆU
Bắt đầu từ phiên bản Oracle 9i, ta có thể thêm mới dòng dữ liệu và đặt giá trị mặc định
thông qua từ khoá DEFALT
Ví dụ:
INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (8000,’MIKE’,DEFAULT);
Oracle còn cho phép thực hiện lệnh INSERT trên đồng thời nhiều table khác nhau, chỉ sử
dụng một câu lệnh DML.
Ví dụ:
o Lệnh INSERT không điều kiện (UNCONDITIONAL)
INSERT ALL
INTO T1 (C1, C2, ...) VALUES (C1, C2, ...) INTO T2 (C1, C2, ...) VALUES
(C1, C2, ...) SELECT C1, C2, ... FROM T9;
Trang 38 / 47
Ví dụ 1:
UPDATE emp SET job = 'SALEMAN', hiredate = sysdate, sal = sal * 1.1
WHERE ename = 'SCOTT';
Ví dụ 2:
UPDATE emp SET comm = (SELECT comm FROM commission C WHERE C.empno =
emp.empno) WHERE empno IN (SELECT empno FROM commission);
Ví dụ 3:
UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = BOSTON'),
(sal, comm) = (SELECT 1.1*AVG(sal),1.5*AVG(comm) FROM emp b WHERE
a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc
= 'DALLAS' OR loc = 'DETROIT');
Ta cũng có thể sử dụng mệnh đề DEFAULT trong câu lệnh cập nhật dữ liệu Ví dụ:
UPDATE EMP SET COMM = DEFAULT;
Chú thích:
Cập nhật các nhân viên ở Dallas hoặc Detroit
Thay DEPTNO của các nhân viên này bằng DEPTNO của Boston
Thay lương mỗi nhân viên bằng lương trung bình của bộ phận * 1.1
Thay commission của mỗi nhân viên bằng commission trung bình của bộ phận * 1.5
Trang 39 / 47
Lệnh DML thao tác dữ liệu
Lệnh DDL định nghĩa dữ liệu
Lệnh DCL điều khiển truy nhập dữ liệu
Một giao dịch bắt đầu khi một lệnh SQL được thực hiện Một giao dịch kết thúc một trong
các trường hợp sau:
COMMIT hoặc ROLLBACK
Các lệnh DDL và DCL thực hiện (tự động commit)
Lỗi, thoát khỏi SQL*Plus, hệ thống bị down.
Cú pháp:
Kết thúc giao dịch hiện tại, thực hiện các chuyển đổi dữ liệu
COMMIT
Xác định điểm savepoint của giao dịch
SAVEPOINT name
Quay lại dữ liệu ở điểm SAVEPOINT hoặc toàn bộ giao dịch.
ROLLBACK [TO SAVEPOINT name]
Tự động COMMIT khi thực hiện các lệnh Insert, update, delete.
SET AUTO[COMMIT] ON/OFF
Ví dụ:
INSERT INTO DEPT VALUES (50,’TESTING’,’LAS VEGAS’);
SAVEPOINT INSERT_DONE;
UPDATE DEPT SET DNAME = ‘MARKETING’;
ROLLBACK TO INSERT_DONE ;
UPDATE DEPT SET DNAME = ‘MARKETING’ WHERE DNAME =’SALES’;
COMMIT;
Trang 40 / 47
9 SEQUENCE VÀ INDEX
9.1 SEQUENCE
Với:
INCREMENT BY Chỉ định khoảng cách của dãy số tuần tự
START WITH Chỉ định số đầu tiên của dãy số tuần tự
MAXVALUE Giá trị lớn nhất của dãy tuần tự
MINVALUE Giá trị nhỏ nhất của dãy tuần tự
CYCLE/NO CYCLE Dãy tuần tự có quay vòng khi đến điểm cuối.
Mặc định là NO CYCLE
Ví dụ:
CREATE SEQUENCE sample_sequence INCREMENT 1 STRAT WITH 2 MAXVALUE 100;
Để làm việc với các sequence, dùng lệnh SQL với các cột giả sau
CURRVAL Cho giá tri hiện thời của sequence
NEXTVAL Tăng giá tri hiện thời của sequence và cho giá trị sau khi
tăng phải xác định tên sequence trước currval và nextval
sequence.CURRVAL
sequence.NEXTVAL
Để truy cập các sequence không thuộc schema hiện thời, thì phải chỉ ra tên schema
schema.sequence.CURRVAL
schema.sequence.NEXTVAL
Để truy cập các sequence từ xa, thì còn phải chỉ ra datalink
schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink
Sử dụng sequence
CURRVAL và NEXTVAL có thể được sử dụng trong các trường hợp sau:
Trong danh sách lựa chọn của câu lệnh SELECT
Trang 41 / 47
Trong mệnh đề VALUES của câu lệnh INSERT
Trong mệnh đề SET của câu lệnh UPDATE
Không được sử dụng CURRVAL và NEXTVAL trong các trường hợp sau
Trong câu hỏi con
Trong các view và snapshot
Trong câu lệnh SELECT có tác tử DISTINCT
Trong câu lệnh SELECT có sử dụng GROUP BY hay ORDER BY
Trong câu lệnh SELECT có sử dụng các phép toán tập hợp như UNION, INTERSET,
MINUS
Trong mệnh đề WHERE của câu lệnh SELECT
Gía trị DEFAULT của cột trong câu lệnh CREATE TABLE hay ALTER TABLE
Trong điều kiện của ràng buộc CHECK
Huỷ sequence:
DROP SEQUENCE sequence_name ;
9.2 INDEX
Trang 42 / 47
Thêm một row vào bảng tất cả các Index sẽ được update. Nên chọn lựa giữa yêu cầu
query, và insert, update để có một index hợp lý. Đối với các khoá PRIMARY KEY và
UNIQUE KEY từ khoá UNIQUE được tự động thêm khi tạo INDEX.
Ví dụ:
CREATE INDEX i-ENAME ON EMP (ENAME);
Xoá INDEX bằng lệnh:
DROP INDEX index_name ;
Trang 43 / 47
10 VIEWS
10.1 VIEWS
Trong đó:
OR REPLACE Để tạo view chèn lên view cùng tên
FORCE Để tạo view cả khi table hay view nào đó không tồn tại
trong câu lệnh SELECT.
column, column Tên các column của view
WITH CHECK OPTION Nếu có lệnh insert hoặc update lên view, ql sẽ kiểm
tra điều kiện phù hợp trong mệnh đề where của view. Nếu không dữ liệu sẽ chỉ
kiểm tra các ràng buộc toàn vẹn của bảng.
CONSTRAINT Chỉ ra tên của điều kiện kiểm tra.
Ví dụ 1:
CREATE VIEW emp_view AS SELECT empno, ename, sal FROM emp WHERE deptno =
10;
Ví dụ 2:
CREATE VIEW dept_summary (name, minsal, maxsal, avsal) AS
SELECT dname, min(sal), max(sal), avg(sal) FROM emp, dept FROM emp, dept
WHERE emp.deptno = dept.deptno GROUP BY dname;
Ví dụ 3:
CREATE VIEW dept_view AS SELECT eame, sal*12 Annsal
FROM emp WHERE deptno = 20 WITH CHECK OPTIION CONSTRAINT dept_check;
Trang 44 / 47
INSERT (insert trên view cũng ảnh hưởng lên table) Update (ảnh hưởng lên
table)
Comment
Trang 45 / 47
11 QUYỀN VÀ BẢO MẬT
CSDL: Khi cài đặt xong hệ quản trị CSDL Oracle mặc định đã có 2 user.
SYS: Có quyền cao nhất. Mạt khẩu là change_on_install
SYSTEM: Có quyền thấp hơn SYS. Mật khẩu là MANAGER
Quyền hệ thống
Trong các quyền hệ thống quyền DBA là lớn nhất. DBA có quyền
CREATE USER : Tạo user mới
DROP USER :Xoá user
DROP ANY TABLE :Xoá table
BACKUP ANY TABLE :Tạo các backup table.
Trang 46 / 47
GRANT privilege[,privilege...] TO user [,user...]
Xoá quyền
REVOKE privilege[,privilege...] FROM user [,user...]
11.2 ROLE
Role là tên của một nhóm các quyền hạn. Nó được tạo để quản lý quyền hạn cho các ứng
dụng hoặc nhóm các User. Việc dùng role cho phép quản lý thống nhất trên các đối tượng,
tăng tính mềm dẻo trong quản trị, dễ dàng thay đổi. Ví dụ hai đối tượng X, Y có quyền
trên role A tức là role A có quyền gì thì X, Y có quyền tương ứng khi role A bị thay đổi
quyền hạn thì X, Y cũng bị thay đổi quyền hạn theo.
Lệnh tạo Role
Cú pháp:
CREATE ROLE role [IDENTIFY BY password];
Lệnh gán và xoá Role giống như lệnh gán và xoá Privilege. Chi tiết xem trong phần quản
trị Oracle.
11.3 SYNONYM
Synonyms là bí danh cho mọi đối tượng của Oracle. Các đối tượng của Oracle là table,
view, snapshot, sequence, procedure, function, package và các synonym khác. Cú pháp
CREATE PUBLIC SYNONYM synonym_name FROM [OWNER.]object_name;
Tính bảo mật là vì synonym là bí danh, nên người sử dụng dùng bí danh này sẽ không
đoán được thêm thông tin gì.
Trang 47 / 47