oracle 5种约束条件使用方法模板.docx
《oracle 5种约束条件使用方法模板.docx》由会员分享，可在线阅读，更多相关《oracle 5种约束条件使用方法模板.docx（12页珍藏版）》请在冰豆网上搜索。
oracle5种约束条件使用方法模板
1约束
约束是操作表数据的强制规定
有以下五种约束:
•NOTNULL;非空约束
•UNIQUE：
唯一值约束
•PRIMARYKEY：
主键约束
•FOREIGNKEY：
外键约束
•CHECK：
检查约束
约束：
建表的时候可以加约束，建表后也可加约束
注意事项
、如果不指定约束名Oracleserver自动按照S、YS_Cn的格式指定约束名
、在什么时候创建约束:
•建表的同时
•建表之后
、可以在表级或列级定义约束
、可以通过数据字典视图查看约束
createtablenewdept
as
select*fromdepartments
createtablenewemp
as
select*fromemployees
2定义约束
CREATETABLE[schema.]table
（columndatatype[DEFAULTexpr]
[column_constraint],
...
[table_constraint][,...]）;
CREATETABLEemployees（
employee_idNUMBER（6）,
first_nameVARCHAR2（20）,
...
job_idVARCHAR2（10）NOTNULL,
CONSTRAINTemp_emp_id_pk
PRIMARYKEY（EMPLOYEE_ID））;
列级
column[CONSTRAINTconstraint_name]constraint_type,
表级
column,...
[CONSTRAINTconstraint_name]constraint_type
（column,...）,
3NOTNULL约束
只能定义在列级:
保证列值不能为空:
修改first_name非空
altertablenewemp
modify（first_nameNOTNULL）
取消非空约束
altertablenewemp
modify（first_nameNULL）
增加约束
定义每一行必须满足的条件
以下的表达式是不允许的:
•出现CURRVAL,NEXTVAL,LEVEL,和ROWNUM伪列
•使用SYSDATE,UID,USER,和USERENV函数
•在查询中涉及到其它列的值
...,salaryNUMBER
（2）
CONSTRAINTemp_salary_min
CHECK（salary>0）,...
限制工资不能少于2000
altertablenewemp
ADDconstraintsnewemp_salary_ckCHECK（salary>=2000）
添加约束的语法
使用ALTERTABLE语句:
添加或删除约束,但是不能修改约束
有效化或无效化约束
添加NOTNULL约束要使用MODIFY语句
ALTERTABLEtable
ADD[CONSTRAINTconstraint]type（column）;
添加约束
ALTERTABLEemployees
ADDCONSTRAINTemp_manager_fk
FOREIGNKEY（manager_id）
REFERENCESemployees（employee_id）;
altertable表名
modify（自定义列名约束设置）
CREATETABLEemployees（
employee_idNUMBER（6）,
last_nameVARCHAR2（25）NOTNULL,
salaryNUMBER（8,2）,
commission_pctNUMBER（2,2）,
hire_dateDATE
CONSTRAINTemp_hire_date_nn
NOTNULL,
4主键约束
限制列值不允许空值，且不能为空，一个表只有一个主键
只要主键都会Alter，一般主键是ID唯一的
altertablenewemp
ADDconstraintsnewemp_empid_pkprimarykey（employee_id）
删除主键约束
altertablenewemp
dropconstraintsnewemp_empid_pk;
复合主键（多个列设置为主键）
ALtertablenewemp
addconstraintnewemp_ename_pkprimarykey（first_name,last_name）
5唯一约束
唯一值约束，限制列值不允许重复，可以为空（不会限制空值），一个表可以有多个唯一值的约束，不允许insertinto
可以定义在表级或列级:
设置唯一值约束
altertablenewemp
ADDconstraintsnewemp_email_ukUNIQUE（email）
CREATETABLEdepartments（
department_idNUMBER（4）,
department_nameVARCHAR2（30）
CONSTRAINTdept_name_nnNOTNULL,
manager_idNUMBER（6）,
location_idNUMBER（4）,
CONSTRAINTdept_id_pkPRIMARYKEY（department_id））;
查约束，限制自定义条件，忽略空值
可以定义在表级或列级:
CREATETABLEemployees（
employee_idNUMBER（6）,
last_nameVARCHAR2（25）NOTNULL,
emailVARCHAR2（25）,
salaryNUMBER（8,2）,
commission_pctNUMBER（2,2）,
hire_dateDATENOTNULL,
...
CONSTRAINTemp_email_ukUNIQUE（email））;
6外键约束
一般都是主键的ID不能修改
可以定义在表级或列级:
FOREIGNKEY:
在表级指定子表中的列
REFERENCES:
标示在父表中的列，后面跟一样的主键
ONDELETECASCADE:
当父表中的列被删除是，子表中相对应的列也被删除
ONDELETESETNULL:
子表中相应的列置空
CREATETABLEemployees（
employee_idNUMBER（6）,
last_nameVARCHAR2（25）NOTNULL,
emailVARCHAR2（25）,
salaryNUMBER（8,2）,
commission_pctNUMBER（2,2）,
hire_dateDATENOTNULL,
...
department_idNUMBER（4）,
CONSTRAINTemp_dept_fkFOREIGNKEY（department_id）
REFERENCESdepartments（department_id）,
CONSTRAINTemp_email_ukUNIQUE（email））;
主外键关联
外键约束:
限制外键列值必须是引用主键中的主键列存在的列值，不限制空值
ALtertablenewdept
addconstraintnewdeptidprimarykey（department_id）
altertablenewemp
部门主键约束设置
ADDCONSTRAINTSnewemp_deptid_fkFOREIGNKEY（department_id）//newemp_deptid_fk鼠标点击名称
referencesnewdept（department_ID）;//关联想要给newemp增加外键的前提是newdept必须有主键，先设置newdept的主键之后才会有外键
7级联删除
删除约束
从表EMPLOYEES中删除约束
ALTERTABLEemployees
DROPCONSTRAINTemp_manager_fk;
使用CASCADE选项删除约束
外键和主键一起删除
在ALTERTABLE语句中使用DISABLE子句将约束无效化。
使用CASCADE选项将相关的约束也无效化
ALTERTABLEemployees
DISABLECONSTRAINTemp_emp_id_pkCASCADE;//中间的是约束的名字
ALTERTABLEdepartments
DROPPRIMARYKEYCASCADE;
altertablenewemp
addconstraintsnewemp_deptid_fkforeignkey（department_id）
referencesnewdept（department_id）//REFERENCES关联的是父键dept所以是department_id
ondeletecascade
级联置空
员工不删除但是把外键删除
altertablenewemp
addconstraintsnewemp_deptid_fkforeignkey（department_id）
referencesnewdept（department_id）
ondeletesetnull//引用之前必须drop掉外键在一块执行
ALTERTABLEnewemp
ADDCONSTRAINTSnewemp_deptid_fkFOREIGNKEY（department_id）
REFERENCESnewdept（department_id）
ONDELETECASCADE;//必须删除外键之后才能执行语句
ALTERTABLEnewemp
ADDCONSTRAINTSnewemp_deptid_fkFOREIGNKEY（department_id）
REFERENCESnewdept（department_id）
ONDELETESETNULL;//想要删除不能按钮drop要用下面的语句删除外键
altertablenewemp
dropCONSTRAINTSnewemp_deptid_fk;
8激活约束
在ALTERTABLE语句中使用DISABLE子句将约束无效化。
使用CASCADE选项将相关的约束也无效化
ENABLE子句可将当前无效的约束激活
ALTERTABLEemployees
ENABLECONSTRAINTemp_emp_id_pk;
Tablealtered.
当定义或激活UNIQUE或PRIMARYKEY约束时系统会自动创建UNIQUE或PRIMARYKEY索引
无效化约束
Altertablenewemp
disableconstraintnewemp_ename_pk
激活约束
Altertablenewemp
enableconstraintnewemp_ename_pk
9查询约束
查询数据字典视图USER_CONSTRAINTS
SELECTconstraint_name,constraint_type,
search_condition
FROMuser_constraints
WHEREtable_name='EMPLOYEES';
查询定义约束的列
SELECTconstraint_name,column_name
FROMuser_cons_columns
WHEREtable_name='EMPLOYEES';
10建表的时候增加约束
CREATEtableemp2（
emp_idnumber（10）primarykey,设置主键
emp_namevarchar2（60）NOTNULL,非空约束
emp_emailvarchar2（50）UNIQUE,唯一值约束
emp_salnumber（8,2）check（emp_sal>=500）,检查约束
emp_deptidnumber（6）referencesnewdept（department_id）外键约束
constraintsemp2_emp2_ukunique（emp_email）
）//,没有完成要加，强制约束条件同primarykey
11索引:
主键唯一的，索引方便快速的查询，关联的机构，加快速度，目录索引
方便地位查找，给小范围寻找
索引还会往下查
作用:
加速查询，但是会降低DML操作
一种数据库对象
通过指针加速Oracle服务器的查询速度
通过快速定位数据的方法，减少磁盘I/O
索引与表相互独立
Oracle服务器自动使用和维护索引
创建索引
、自动创建:
在定义PRIMARYKEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引
、手动创建:
用户可以在其它列上创建非唯一的索引，以加速查询
索引分类
B-tree索引默认，适用于基数比较高，冗余数量较少的时候
位图索引，适用于基数比较第，冗余数量较高的时候
适合创建B-TREE索引的时机：
1.列值基数比较高（重复率比较低）
2.不以空值做条件
3.列经常出现在条件子句中
4.表很大
5.经常做查询操作，而不是DML操作
6.满足条件的数据不超过总记录数的15%
7.模糊查询不走索引，如果模糊查询想使用索引，必须用全文检索技术
创建索引
在一个或多个列上创建索引
CREATEINDEXindex
ONtable（column[,column]...）;
在表EMPLOYEES的列LAST_NAME上创建索引
CREATEINDEXemp_last_name_idx
ONemployees（last_name）;
Indexcreated.
创建唯一索引
altertablenew_emp
addconstraintsnewemp_empid_pkprimarykey（employee_id）
select*fromnew_emp
创建普通索引
createindexemp_lastname_idxonnew_EMP（last_name）
查询索引
可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息
SELECTic.index_name,ic.column_name,
ic.column_positioncol_pos,ix.uniqueness
FROMuser_indexesix,user_ind_columnsic
WHEREic.index_name=ix.index_name
ANDic.table_name='EMPLOYEES';
select*fromnew_empwhereemployee_id=174
select*fromnew_empwherelast_name='King'
//King==king
创建基于函数的索引
createindexnewemp_lastname_idxonnew_emp（LOWER（last_name））
select*fromnew_empwhereLOWER（last_name）='King'
基于函数的索引
基于函数的索引是一个基于表达式的索引
索引表达式由列,常量,SQL函数和用户自定义的函数
CREATEINDEXupper_dept_name_idx
ONdepartments（UPPER（department_name））;
Indexcreated.
SELECT*
FROMdepartments
WHEREUPPER（department_name）='SALES';
位图索引
位图索引是在基数较小，即重复率很大的列值中创建比较适用
createbitmapindexemp_jobid_bmidx
onempcopy（job_id）;
createindexnewemp_jobid_idxonnew_emp（job_id）
select*fromnew_empwherejob_id='IT_PROG'
RBO和CBO
RBO：
•基于规则的优化方式（Rule-BasedOptimization）
CBO:
•基于代价的优化方式（Cost-BasedOptimization）
删除索引
使用DROPINDEX命令删除索引
DROPINDEXindex
删除索引UPPER_LAST_NAME_IDX
DROPINDEXupper_last_name_idx;
Indexdropped.
只有索引的拥有者或拥有DROPANYINDEX权限的用户才可以删除索引
dropindexemp_lastname_idx//
12用户和权限
权限
数据库安全性:
•系统安全性
•数据安全性
系统权限:
对于数据库的权限
对象权限:
操作数据库对象的权限
方案:
一组数据库对象集合,例如表,视图,和序列
系统权限
超过一百多种100有效的权限
数据库管理员具有高级权限以完成管理任务，例如:
•创建新用户
•删除用户
•删除表
•备份表
创建用户
Cmd下
SQLPLUS
System/scott小写
GrantcreateusertoSCOTT
GrantcreatesessiontoSCOTT
Revokecreatesessiontoicss；
在PL里面输入权限创建用户
createusericssidentifiedbyicss
createtablea（
idnumber
（2）
）
insertintoavalues
（1）
在DOS下输入
Grantcreatesessiontoicss
grantconnect,resourcetoicss;
alterusericssidentifiedby123456
接触锁定
Sqlplus
SCOTT/TIGER
Connsystem/scott;
SCOTTSCOTT/TIGER;
解锁
AlteruserSCOTTACCOUNTUNLOCK;
权限
Grantcreatesessiontoxxx
改密码
Alteruser用户名identifiedby新密码
升级会员 