- --数据库对象的创建和管理 DDL(数据定义语言)
- --表(table): 数据库存储的基本单元;
- --约束条件(constraint):用来确保数据库中数据的完整性,确保数据满足某些特定的商业规则
- --视图(view):一个或多个表的逻辑表示或虚拟表示,主要用于简化查询操作
- --索引(index):用于加速数据访问数据库对象,提高访问效率
- --序列(sequence):用于生成唯一数字值的数据库对象,序列的生成机制会自动生成顺序递增的数字,可以用来作为数据表的主键值
- --同义词(synonym):对象别名
- --数据类型: char,varchar,number,date,timestamp(默认的显示格式:DD-Mon-RR HH12.MI.SS AM)
- -- blob(二进制数据大对象类型) clob(字符大对象类型 <=4GB) bfile(数据库外部二进制文件)
- --数据字典 一些只读的表和视图
- --可参考http://www.cnblogs.com/jonescheng/archive/2008/03/24/1119380.html
- --racle中的数据表可以分为两大类:用户表和数据字典表.用户表:用户创建和维护,
- --数据字典表:由Oracle数据库自己创建和维护,存放数据库自身信息,包括描述数据库和它所有对象的信息,
- --以及一些统计分析数据库的视图等.
- --命名规则
- --DBA_***:指整个数据库包含的对象信息
- --DBA_TABLES:数据库中全部数据表 select count(1) from dba_tables; ==>2784
- --DBA_OBJECTS:数据库中全部对象 select * from dba_objects where owner ='SCOTT';
- --SCOTT创建的所有表和索引都在里面
- --DBA_DATA_FILES:数据库文件信息
- --ALL_**用户可以访问的对象信息 除了当前用户自己方案中的表外,还可以访问其它用户方案下有权限的表
- --ALL_TABLES;
- --ALL_INDEXES;
- --ALL_OBJECTS;
- --USER_***用户自己方案下的数据表
- --USER_TABLES;
- --USER_VIEWS;
- --USER_OBJECTS;
- --查询数据字典
- describe dictionary;
- --desc dict; 简写
- --查询数据字典里面的 USER_TABLES 表的信息
- select * fromdictionarywheretable_name='USER_TABLES';
- 二、创建表
- --create table [schema.]table_name(column_name datetype [default expr] [,....]);
- --这里的schema默认为当前用户的方案,也可以制定其它用户
- --通过子查询创建表
- create tablescott.emp30as select * fromempwheredeptno=30;
- --基本创建表 无法执行,和命名空间有关系,等学完oracle整个结构在深入
- --encrypt 对数据的加密保存,待深入
- CREATE TABLE hr.admin_emp (
- empno NUMBER(5)PRIMARY KEY,
- ename VARCHAR2(15)NOT NULL,
- sex CHAR(1)default 'M',
- ssn NUMBER(9) ENCRYPT,
- job VARCHAR2(10),
- mgr NUMBER(5),
- hiredate DATE DEFAULT (sysdate),
- photo BLOB,
- sal NUMBER(7,2),
- hrly_rate NUMBER(7,2) GENERATED ALWAYSAS(sal/2080),
- comm NUMBER(7,2),
- deptno NUMBER(3)NOT NULL
- CONSTRAINTadmin_dept_fkeyREFERENCES hr.departments
- (department_id))
- TABLESPACE admin_tbs
- STORAGE ( INITIAL 50K);
- --应该是往数据字典里面添加commentCOMMENTON TABLEhr.admin_empIS 'Enhanced employee table';
- --三、修改表
- --增加列
- alter tableemp10add(sexchar(1)default 'M');
- --增加表的constraint 因为列级的约束,可以直接通过修改列修改
- --alter table table_name add[CONSTRAINT constraint type (column,...)];
- --修改列
- alter tableemp10 renamecolumnempnoto id;
- alter tableemp10 modify(jobvarchar(20)default 'clerk');
- --删除列 必需保证该列下面的数据为空
- alter tableemp10drop (comm);
- --修改表名rename emp10to employee10;
- --四、截断表 删除该表下面的所有数据
- --truncate 速度很快,并不在事务日志中记录所删除的数据,所以不能恢复,delete会在日志中记录删除操作,
- truncate table employee10;
- --五、删除表
- --drop table table_name [cascade constraint]
- --如果表被其它表参考(外键..) 需要使用 CASCADE CONSTRAINT;
- drop table employee10;
- --六、给表增加注释
- --comment on {table|column} {table_name|tablename.column} is 'comment_string';
- comment on tableemp_20is '部门编号是20的员工';
- --七、在数据字典中查看表信息
- --和表相关的数据字典有 USER_TABLES; USER_OBJECTS; USER_TAB_COMMENTS; USER_COLUMN_COMMENTS;
- selecttable_namefrom user_tables;
- select * fromuser_tab_commentswheretable_name='EMP_20';
- --八、约束条件
- --create table [scheme.] table_name(column_name datatype[default expr] [column_constraint],
- [,...],
- [table_constraint]
- );
- --表级约束和列级约束语法如下:column_constraint=[CONSTRAINT constraint_name] constraint_type;
- table_constraint =[CONSTRAINT constraint_name]constraint_type(column,...);
- 一、约束类型not null,unique,primary key,foreign key,check
- --1.not null
- create table employees (
- eid number(6),
- --这个not null 约束由系统命名namevarchar(20)not null,
- salary number(3,2),
- --给not null约束起了一个名字(employees_hiredate_nn 规则:表名_列名_约束条件名称)hiredate dateCONSTRAINTemployees_hiredate_nnnot null
- );
- --在数据字典中查看constraint
- selectowner, constraint_name, constraint_typefromuser_constraintswheretable_name='EMPLOYEES';
- --2.unique 规定了唯一constraint,Oracle数据库会自动建立一个索引,索引名称和约束名称相同
- --给employees添加一列email并添加约束unique
- --1.直接把约束添加到lie中,
- --2.给表添加约束,可以同时给两列添加约束,此时两列组合为键,
- alter tableemployeesadd(emailvarchar(15)unique);
- --alter table employees add(email varchar(15));
- --alter table employees add CONSTRAINT employees_email_uk unique(name);
- --3.primary key 不能为null且unique 可是使用联合主键,两个列为主键
- --4.froeign key 定义在一个表的两个字段(自身关联),或者两个表的一个字段
- alter tableemployeesadd(deptnonumber(4));
- alter tableemployeesadd CONSTRAINTemployees_deptno_fkforeign key(deptno)REFERENCES dept(deptno);
- --select * from user_cons_columns where table_name='EMPLOYEES';
- --on delete cascade 删除主表值时,会删除从表的值
- --ondelete set null 删除主表时,把从表的值置为null
- -- alter table employees add constraint employess_deptno_fk foreign key(deptno)
- referencesdept(deptno)[on delete cascade | on delete set null];
- --5.check 检查用来描述字段上的每个值都要满足check中定义的条件
- alter tableemployeesadd CONSTRAINTemployees_salary_ckcheck(salary>800);
- insert intoemployees(eid,name, salary, hiredate, deptno)selectempno, ename, sal, hiredate, deptnofromempwherehiredateis not null andsal>800;
- --启用和禁用约束 暂时让约束失效,在批量导数据是有用
- -- alter table table_name disable |enable CONSTRAINT constraint_name [cascade];
- --cascade是指在关闭约束后,对外键的联级也消失
- --显示该表所有约束
- selectc1.constraint_name,c2.constraint_type,c1.column_namefromuser_cons_columns c1,user_constraints c2wherec1.table_name='EMPLOYEES' andc1.constraint_name=c2.constraint_name;
- --关闭所有约束
- alter tableemployees disableconstraint EMPLOYEES_SALARY_CK;
- alter tableemployees disableconstraintemployees_deptno_fkcascade;
- alter tableemployees disableconstraint sys_c0011178;
- alter tableemployees disableconstraint sys_c0011176;
- alter tableemployees disableconstraint employees_hiredate_nn;
- alter tableemployees enableconstraint employees_hiredate_nn;
- insert intoemployees(eid,name, salary, hiredate, deptno)selectempno, ename, sal, hiredate, deptnofrom emp;
- --导入数据后发现部分约束打不开了,因为数据不正常了
- alter tableemployees enableconstraint EMPLOYEES_SALARY_CK;
- alter tableemployees enableconstraint employees_deptno_fk ;
- alter tableemployees enableconstraint sys_c0011178;
- alter tableemployees enableconstraint sys_c0011176;
- --九、视图:虚表,一条查询语句得到的结果集.视图只包含映射导基表的一个查询语句,可以执行dml语句
- --优点:简化复杂查询,经常在多表上面执行发杂查询,就可以基于复杂查询创建视图,之后查询视图就好了
- 1.创建视图
- create [ or replace] viewview_name[(col_alias[,col_alias])]
- assubquery[with read only];
- --create创建一个view,create or replace 修改视图col_alias定义视图中列的别名,with read only 表示不能执行dml语句
- create viewv_emp_10as selectempno, ename, sal salary, deptnofromempwheredeptno=10;
- select * from v_emp_10;
- --获得每个部门的平均薪水和薪水总和,最高薪水,最低薪水的视图
- create or replace viewv_emp_salaryas selectdeptno,avg(sal) avg_sal,
- sum(sal) sum_sal,max(sal)max,min(sal)min fromempgroup by deptno;
- --获得每个部门的平均薪水和薪水总和,最高薪水,最低薪水的视图 使用视图别名
- create or replace viewv_emp_salary (deptno,avg,sum,max,min)as
- selectdeptno,avg(sal),sum(sal),max(sal),min(sal)fromempgroup by deptno;
- 2.删除视图
- drop view v_emp_10;
- 3.数据字典中查看视图 user_objects, user_views,user_updatable_columns,
- select object_name fromuser_objectswhereobject_type='VIEW';
- selectview_name,text from user_views;
- --因为包含了group by语句,所以这个并不是基表的直接映射,所以不能修改
- selectcolumn_name, insertable, updatable, deletablefromuser_updatable_columnswheretable_name='V_EMP_SALARY';
- --十、索引 应该是把索引和地址放在一个表里面,快速访问
- --用来在数据库中加速表查询的数据库对象,通过夸苏路径访问方式快速定位数据,可以有效的减少磁盘I/O操作,提高性能.
- --DML操作将会更新索引,增加了dml的时间.
- --创建索引的原则:
- 1.为经常出现在where,order by,distinct子句中的列创建索引,
- 2.连接条件的列加上索引
- 3.不要在经常做dml操作的表加索引,不要在小表上面建索引,限制表的索引数目,
- 1.创建索引
- create [unique] indexindex_nameon table(column[,column...]);
- create indexidx_emp_enameon emp(ename);
- selectempno, ename, sal, jobfromempwhereename='SMITH';
- 2.重构索引 经常做dml操作的表,需要定期重建索引
- alter index idx_emp_ename rebuild;
- 3.删除索引
- drop index idx_emp_ename;
- 4.在数据字典中查看索引 user_indexes, user_ind_columns;
- selectindex_namefromuser_indexeswheretable_name='EMP';
- 十一、序列
- 用来生成唯一序列值的数据库对象,一般用来自动生成表的主键,
- --创建序列
- createsequence[schema.] sequence_name
- [start with i] [increament by j]
- [maxvalue m |nomaxvalue]
- [minvalue n |nominvalue]
- [cycle | nocycle] [cache p | nocache]
- 第一个值的序列i 步数j 最大值m 最小值n
- cycle 递增至最大或者递减至最小之后是否继续生成序列号
- cache 用来制定预取p个数据在缓存中,以提高序列的生成效率
- --修改序列
- altersequence[schema.] sequence_name
- [increament by j]
- [maxvalue m |nomaxvalue]
- [minvalue n |nominvalue]
- [cycle | nocycle] [cache p | nocache]
- --删除序列
- drop sequence sequence_name;
- --数据字典
- user_sequence
- 十二、同义词
- 1.创建共用同义词
- create publicsynonym synonym_namefor [schema.]object;
- create publicsynonym public_empfor emp;
- select * from public_emp;
- 2.创建私有的同义词 只用当前用户可以直接使用,其它用户访问需要该同义词对应表的访问权限
- createsynonym private_empfor emp;
- select * from private_emp;
- 3.删除同义词
- drop public synonym public_emp;
- drop synonym private_emp;
- 4.数据字典中查看
- user_synonym;
- --参考书籍Oracle 11g数据库编程与实践 宁丽娟
来源: http://www.bubuko.com/infodetail-2072116.html