oracle表管理.docx
《oracle表管理.docx》由会员分享，可在线阅读，更多相关《oracle表管理.docx（14页珍藏版）》请在冰豆网上搜索。
oracle表管理
1命名规则
表名和列名:
必须以字母开头
必须在1–30个字符之间
必须只能包含A–Z,a–z,0–9,_,$,和#
必须不能和用户定义的其他对象重名
必须不能是Oracle的保留字
CREATETABLE语句
必须具备:
•CREATETABLE权限
•存储空间
CREATETABLE[schema.]table
（columndatatype[DEFAULTexpr][,...]）;
必须指定:
表名
列名,数据类型,尺寸
引用其他用户的表
其他用户定义的表不在当前用户的方案中
应该使用用户名作为前缀，引用其他用户定义的对象
DEFAULT选项
插入时为一个列指定默认值
...hire_dateDATEDEFAULTSYSDATE,...
字符串,表达式,或SQL函数都是合法的
其它列的列名和伪列是非法的
默认值必须满足列的数据类型定义
创建表
CREATETABLEdept_（deptnoNUMBER
（2）,
dnameVARCHAR2（14）,
locVARCHAR2（13））;
Tablecreated.
2Oracle数据库中的表
用户定义的表:
•用户自己创建并维护的一组表
•包含了用户所需的信息
数据字典:
•由OracleServer自动创建的一组表
•包含数据库信息
查询数据字典
SELECTtable_name
FROMuser_tables;
SELECTDISTINCTobject_type
FROMuser_objects;
SELECT*
FROMuser_catalog;
3数据类型
VARCHAR2（size）可变长字符数据
CHAR（size）定长字符数据
NUMBER（p,s）可变长数值数据
DATE日期型数据
LONG可变长字符数据，最大可达到2G
CLOB字符数据，最大可达到4G
RAWandLONGRAW裸二进制数据
BLOB二进制数据，最大可达到4G
BFILE存储外部文件的二进制数据，最大可达到4G
ROWID行地址
4使用子查询创建表
时候用ASsubquery选项，将创建表和插入数据结合起来
CREATETABLEtable
[（column,column...）]
ASsubquery;
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
CREATETABLEdept80
AS
SELECTemployee_id,last_name,
salary*12ANNSAL,
hire_date
FROMemployees
WHEREdepartment_id=80;
5ALTERTABLE语句
使用ALTERTABLE语句可以:
追加新的列
修改现有的列
为新追加的列定义默认值
删除一个列
使用ALTERTABLE语句追加,修改,或删除列的语法.
ALTERTABLEtable
ADD（columndatatype[DEFAULTexpr]
[,columndatatype]...）;
ALTERTABLEtable
MODIFY（columndatatype[DEFAULTexpr]
[,columndatatype]...）;
ALTERTABLEtable
DROP（column）;
追加一个新列
使用ADD子句追加一个新列
ALTERTABLEdept80
ADD（job_idVARCHAR2（9））;
新列是表中的最后一列
修改一个列
可以修改列的数据类型,尺寸,和默认值
ALTERTABLEdept80
MODIFY（last_nameVARCHAR2（30））;
对默认值的修改只影响今后对表的修改
删除一个列
使用DROPCOLUMN子句删除不再需要的列.
ALTERTABLEdept80
DROPCOLUMNjob_id;
删除表
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROPTABLE语句不能回滚
DROPTABLEdept80;
执行RENAME语句改变表,视图,序列,或同义词的名称
RENAMEdeptTOdetail_dept;
必须是对象的拥有者
清空表
TRUNCATETABLE语句:
•删除表中所有的数据
•释放表的存储空间
TRUNCATE语句不能回滚
可以使用DELETE语句删除数据
TRUNCATETABLEdetail_dept;
表的注释
使用COMMENT语句给表或列添加注释
⏹可以通过下列数据字典视图查看所添加的注释:
•ALL_COL_COMMENTS
•USER_COL_COMMENTS
•ALL_TAB_COMMENTS
•USER_TAB_COMMENTS
COMMENTONTABLEemployees
IS'EmployeeInformation';
6视图
视图：
虚拟表，作用是保存查询结果，仅保存查询语句，而不保存真实数据（物化视图）
为什么使用视图
控制数据访问
简化查询
数据独立性
避免重复访问相同的数据
简单视图和复杂视图
特性简单视图复杂视图
表的数量一个一个或多个
函数没有有
分组没有有
DML操作可以有时可以
创建视图
create（orreplace）view视图名称
AS
查询语句
在CREATEVIEW语句中嵌入子查询
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview
[（alias[,alias]...）]
ASsubquery
[WITHCHECKOPTION[CONSTRAINTconstraint]]
[WITHREADONLY[CONSTRAINTconstraint]];
权限问题
createviewv_emp
as
selectemployee_id，last_name，salary
fromemployees
wheredepartment_id=50
进入DOS
Sqlplussystemscott
GrantcreateviewtoSCOTT;
创建一个只读视图
createviewv_emp//视图V_EMP
as
selectemployee_id，last_name，salary
fromemployees
wheredepartment_id=50
WITHreadonly；//只读不能修改
子查询可以是复杂的SELECT语句
select*fromV_EMP
描述视图结构
DESCRIBEempvu80
创建视图时在子查询中给列定义别名
在选择视图中的列时应使用别名
CREATEVIEWsalvu50
ASSELECTemployee_idID_NUMBER,last_nameNAME,
salary*12ANN_SALARY
FROMemployees
WHEREdepartment_id=50;
修改视图
使用CREATEORREPLACEVIEW子句修改视图
CREATEVIEW子句中各列的别名应和子查询中各列相对应
CREATEORREPLACEVIEWempvu80
（id_number,name,sal,department_id）
ASSELECTemployee_id,first_name||''||last_name,
salary,department_id
FROMemployees
WHEREdepartment_id=80;
创建视图V_dept查询每个部门的编号，名称，员工人数，员工工资总和
createorreplaceviewv_dept
as
selectd.department_id,d.department_name,COUNT（e.employee_id）emp_count,SUM（salary）sal_sum//必须用列别名
fromdepartmentsd
innerjoinemployeeseond.department_id=e.department_id
groupbyd.department_id,d.department_name
删除视图
dropview视图名
复杂视图举例
CREATEVIEWdept_sum_vu
（name,minsal,maxsal,avgsal）
ASSELECTd.department_name,MIN（e.salary）,
MAX（e.salary）,AVG（e.salary）
FROMemployeese,departmentsd
WHEREe.department_id=d.department_id
GROUPBYd.department_name;
视图中使用DML的规定
可以在简单视图中执行DML操作
当视图定义中包含以下元素之一时不能使用delete:
•组函数
•GROUPBY子句
•DISTINCT关键字
•ROWNUM伪列
当视图定义中包含以下元素之一时不能使用update:
组函数
GROUPBY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
视图中使用DML的规定
当视图定义中包含以下元素之一时不能使用insert:
组函数
GROUPBY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
表中非空的列在视图定义中未包括
WITHCHECKOPTION子句
使用WITHCHECKOPTION子句确保DML只能在特定的范围内执行
任何违反WITHCHECKOPTION约束的请求都会失败
CREATEORREPLACEVIEWempvu20
ASSELECT*
FROMemployees
WHEREdepartment_id=20
WITHCHECKOPTIONCONSTRAINTempvu20_ck;
屏蔽DML操作
CREATEORREPLACEVIEWempvu10
（employee_number,employee_name,job_title）
ASSELECTemployee_id,last_name,job_id
FROMemployees
WHEREdepartment_id=10
WITHREADONLY;
删除视图只是删除视图的定义，并不会删除基表的数据
7序列
序列共享对象不做全盘扫描
提高查询效率
自动提供唯一的数值
共享对象
主要用于提供主键值
代替应用代码
将序列值装入内存可以提高访问效率
定义序列:
CREATESEQUENCEsequence
[INCREMENTBYn]
[STARTWITHn]
[{MAXVALUEn|NOMAXVALUE}]
[{MINVALUEn|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHEn|NOCACHE}];
创建一个序列
创建序列DEPT_DEPTID_SEQ为表DEPARTMENTS提供主键
不使用CYCLE选项
CREATESEQUENCEdept_deptid_seq
INCREMENTBY10
STARTWITH120
MAXVALUE9999
NOCACHE
NOCYCLE;
创建一个序列
createsequence序列名
startwith100起始值
incrementby10自增长10，默认值1
nomaxvalue最大值，默认内有最大值
nocycle假设设置了最大值，达到最大值之后，是否从头开始，默认nocycle
cache缓存数量默认20
创建一个序列
createsequencestu_seq
查询序列
查询数据字典视图USER_SEQUENCES获取序列定义信息
SELECTsequence_name,min_value,max_value,
increment_by,last_number
FROMuser_sequences;
如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值
NEXTVAL和CURRVAL伪列
NEXTVAL返回序列中下一个有效的值，任何用户都可以引用
CURRVAL中存放序列的当前值
NEXTVAL应在CURRVAL之前指定，二者应同时有效
selectstu_seq.nextvalfromdual
createsequencestu_sql
startwith100
incrementby10
selectstu_seq.nextvalfromdual
删除序列
deletestu
在插入语句中使用序列
INSERTINTOdepartments（department_id,
department_name,location_id）
VALUES（dept_deptid_seq.NEXTVAL,
'Support',2500）;
序列DEPT_DEPTID_SEQ的当前值
SELECTdept_deptid_seq.CURRVAL
FROMdual;
Inserintostuvalues（stu_sql.nextval,'TOM','FEMAL',SYSDATE,'ADDRESS'）
使用序列
⏹将序列值装入内存可提高访问效率
⏹序列在下列情况下出现裂缝:
•回滚
•系统异常
•多个表同时使用同一序列
⏹如果不讲序列的值装入内存（NOCACHE）,可使用表USER_SEQUENCES查看序列当前的有效值
修改序列
修改序列的增量,最大值,最小值,循环选项,或是否装入内存
ALTERSEQUENCEdept_deptid_seq
INCREMENTBY20
MAXVALUE999999
NOCACHE
NOCYCLE;
修改序列的注意事项
必须是序列的拥有者或对序列有ALTER权限
只有将来的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
其它的一些限制
删除序列
使用DROPSEQUENCE语句删除序列
删除之后，序列不能再次被引用
DROPSEQUENCEdept_deptid_seq;
升级会员 