1.约束
约束用来维护数据的完整性,在 Oracle 中 , 有以下五种完整性约束 :
- NOT NULL —— 非空约束
- UNIQUE —— 唯一约束
- PRIMARY KEY —— 主键约束
- FOREIGN KEY —— 外键约束
- CHECK —— 条件约束
可以为一个约束命名 , Oracle 服务器也可以自动为约束命名( SYS_Cn )
约束的创建:当创建表时创建约束、在表创建后仍然可以创建约束
即可以定义 列级 也可以定义 表级 的约束
非空约束是 唯一 一种只能在 列一级 定义的约束
外键约束是用来维护子表和主表之间的 引用完整性
CREATE TABLE myemp(
empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO),
CONSTRAINT emp_dname_uk UNIQUE(ENAME),
CONSTRAINT emp_deptno_ck CHECK (DEPTNO BETWEEN 10 AND 99),
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
添加约束
// 增加唯⼀键值约束
// UNIQUE约束不允许有相同的数据,但允许有多个NULL值,因为每个空值都不等于其它空值
ALTER TABLE dept
ADD CONSTRAINT dept_dname_uk UNIQUE(dname);
// 增加主键约束
ALTER TABLE dept
ADD CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno);
// 创建PRIMARY KEY约束或UNIQUE约束时,系统会自动创建唯一索引
// 增加外键约束
ALTER TABLE emp
ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept (deptno);
删除约束
ALTER TABLE emp
DROP CONSTRAINT emp_mgr_fk;
// 移除DEPT表上的主键约束,因为有CASCADE选项,所以同时删除了在EMP.DEPTNO列上的外键约束
ALTER TABLE dept
DROP PRIMARY KEY CASCADE;
关闭约束(DISABLE子句)
ALTER TABLE emp
DISABLE CONSTRAINT emp_empno_pk CASCADE;
开启约束(使用 ENABLE 子句)
ALTER TABLE emp
ENABLE CONSTRAINT emp_empno_pk;
可以利用数据字典(user_constraints(user_indexes))来获取约束的信息
数据字典 user_cons_columns 可以查出约束定义在哪一列
SELECT owner,constraint_name,constraint_type,table_name
FROM user_constraints;
SELECT owner,constraint_name,table_name,column_name
FROM user_cons_columns;
SELECT owner,constraint_name,table_name,column_name,position
FROM user_cons_columns
WHERE table_name='DEPT';
关于外键
- ON DELETE SET NULL:在删除主表中记录的同时,将子表中所有依赖行的外键值设置为 NULL
- ON DELETE CASCADE:在删除主表中记录的同时 , 删除子表中的所有依赖行
ALTER TABLE emp
ADD CONSTRAINT emp_dept_fk
FOREIGN KEY(deptno) REFERENCES dept(deptno)
ON DELETE SET NULL;
ALTER TABLE emp
ADD CONSTRAINT emp_dept_fk
FOREIGN KEY(deptno) REFERENCES dept(deptno)
ON DELETE CASCADE;
2.视图
从一个或者多个表中产生的一个逻辑数据集
创建视图
Scott用户没有创建视图的权限
connect sys as sysdba
grant create any view to scott;
connect scott/tiger
创建视图,创建的时候可以在列的子查询中使用别名
CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
CREATE VIEW salvu30
AS SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY
FROM emp
WHERE deptno = 30;
从视图中获取数据
SELECT * FROM salvu30;
创建视图后, 视图就被存放在 user_views 数据字典中
SELECT view_name,text_length,text
FROM user_views;
修改视图
Oracle 没有提供直接修改视图的方法,可以使用 CREATE OR REPLACE VIEW 来重建覆盖EMPVU10 视图, 增加别名
CREATE OR REPLACE VIEW empvu10(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
创建复杂视图,视图的数据来自于两个表
CREATE VIEW dept_sum_vu(name, minsal, maxsal, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal),AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
禁⽌对视图DML操作:使用 WITH READ ONLY 选项
CREATE OR REPLACE VIEW empvu10(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10
WITH READ ONLY;
删除视图
DROP VIEW empvu10;
3.序列
用于产生唯一的数字值,是一个可以共享的数据库对象,可用于产生数据库表中的主键值
创建序列
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE
NOCYCLE;
NEXTVAL和 CURRVAL伪列
NEXTVAL 返回下 一个可用的序列值 。每访问一次 , 它将产生一个唯一的新值。
CURRVAL 返回当前的序列值。只有当NEXTVAL被访问之后, CURRVAL 伪列才能包含一个值。
INSERT INTO dept(deptno, dname, loc)
VALUES (dept_deptno.NEXTVAL,'MARKETING', 'SAN DIEGO');
// 查看序列 DEPT_DEPTNO 的当前值
SELECT dept_deptno.CURRVAL
FROM dual;
数据字典 USER_SEQUENCES 可来查看下一个可用的序列值
修改序列
ALTER SEQUENCE dept_deptno
INCREMENT BY 1
MAXVALUE 999999
NOCACHE
NOCYCLE;
删除序列
DROP SEQUENCE dept_deptno;
4.同义词
同义词 : 对象的另一个名字
• 创建同义词可简化对数据库中对象的访问
• 可缩短对象的名字长度
// 创建同义词
CREATE SYNONYM d_sum
FOR dept_sum_vu;
// 丢弃同义词
DROP SYNONYM d_sum;