oracle 5种约束条件使用方法模板.docx
- 文档编号:8158836
- 上传时间:2023-01-29
- 格式:DOCX
- 页数:12
- 大小:42.60KB
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新密码
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 5种约束条件使用方法模板 约束条件 使用方法 模板