--数据库对象的创建和管理 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 * from dictionary where table_name ='USER_TABLES';
二、创建表
--create table [schema.]table_name(column_name datetype [default expr] [,....]);
--这里的schema默认为当前用户的方案,也可以制定其它用户
--通过子查询创建表
create table scott.emp30 as select * from emp where deptno =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 ALWAYS AS (sal/2080),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES hr.departments
(department_id))
TABLESPACE admin_tbs
STORAGE ( INITIAL 50K);
--应该是往数据字典里面添加comment
COMMENT ON TABLE hr.admin_emp IS 'Enhanced employee table';
--三、修改表
--增加列
alter table emp10 add(sex char(1) default 'M');
--增加表的constraint 因为列级的约束,可以直接通过修改列修改
--alter table table_name add[CONSTRAINT constraint type (column,...)];
--修改列
alter table emp10 rename column empno to id;
alter table emp10 modify(job varchar(20) default 'clerk');
--删除列 必需保证该列下面的数据为空
alter table emp10 drop (comm);
--修改表名
rename emp10 to 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 table emp_20 is '部门编号是20的员工';
--七、在数据字典中查看表信息
--和表相关的数据字典有 USER_TABLES; USER_OBJECTS; USER_TAB_COMMENTS; USER_COLUMN_COMMENTS;
select table_name from user_tables;
select * from user_tab_comments where table_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 约束由系统命名
name varchar(20) not null,
salary number(3,2),
--给not null约束起了一个名字(employees_hiredate_nn 规则:表名_列名_约束条件名称)
hiredate date CONSTRAINT employees_hiredate_nn not null
);
--在数据字典中查看constraint
select owner, constraint_name, constraint_type from user_constraints where table_name ='EMPLOYEES';
--2.unique 规定了唯一constraint,Oracle数据库会自动建立一个索引,索引名称和约束名称相同
--给employees添加一列email并添加约束unique
--1.直接把约束添加到lie中,
--2.给表添加约束,可以同时给两列添加约束，此时两列组合为键,
alter table employees add (email varchar(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 table employees add(deptno number(4));
alter table employees add CONSTRAINT employees_deptno_fk foreign 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)
references dept(deptno) [on delete cascade | on delete set null];
--5.check 检查用来描述字段上的每个值都要满足check中定义的条件
alter table employees add CONSTRAINT employees_salary_ck check(salary >800);
insert into employees(eid,name, salary, hiredate, deptno) select empno, ename, sal, hiredate, deptno from emp where hiredate is not null and sal>800;
--启用和禁用约束 暂时让约束失效,在批量导数据是有用
-- alter table table_name disable |enable CONSTRAINT constraint_name [cascade];
--cascade是指在关闭约束后,对外键的联级也消失
--显示该表所有约束
select c1.constraint_name,c2.constraint_type,c1.column_name from user_cons_columns c1,user_constraints c2 where c1.table_name='EMPLOYEES' and c1.constraint_name=c2.constraint_name;
--关闭所有约束
alter table employees disable constraint EMPLOYEES_SALARY_CK;
alter table employees disable constraint employees_deptno_fk cascade;
alter table employees disable constraint sys_c0011178;
alter table employees disable constraint sys_c0011176;
alter table employees disable constraint employees_hiredate_nn;
alter table employees enable constraint employees_hiredate_nn;
insert into employees(eid,name, salary, hiredate, deptno) select empno, ename, sal, hiredate, deptno from emp;
--导入数据后发现部分约束打不开了,因为数据不正常了
alter table employees enable constraint EMPLOYEES_SALARY_CK;
alter table employees enable constraint employees_deptno_fk ;
alter table employees enable constraint sys_c0011178;
alter table employees enable constraint sys_c0011176;
--九、视图:虚表,一条查询语句得到的结果集.视图只包含映射导基表的一个查询语句,可以执行dml语句
--优点:简化复杂查询,经常在多表上面执行发杂查询,就可以基于复杂查询创建视图,之后查询视图就好了
1.创建视图
create [ or replace] view view_name [(col_alias[,col_alias])]
as subquery [with read only];
--create创建一个view,create or replace 修改视图
col_alias定义视图中列的别名, with read only 表示不能执行dml语句
create view v_emp_10 as select empno, ename, sal salary, deptno from emp where deptno=10;
select * from v_emp_10;
--获得每个部门的平均薪水和薪水总和,最高薪水,最低薪水的视图
create or replace view v_emp_salary as select deptno, avg(sal) avg_sal,
sum(sal) sum_sal, max(sal) max, min(sal) min from emp group by deptno;
--获得每个部门的平均薪水和薪水总和,最高薪水,最低薪水的视图 使用视图别名
create or replace view v_emp_salary (deptno, avg, sum, max, min) as
select deptno, avg(sal), sum(sal), max(sal), min(sal) from emp group by deptno;
2.删除视图
drop view v_emp_10;
3.数据字典中查看视图 user_objects, user_views,user_updatable_columns,
select object_name from user_objects where object_type='VIEW';
select view_name, text from user_views;
--因为包含了group by语句,所以这个并不是基表的直接映射,所以不能修改
select column_name, insertable, updatable, deletable from user_updatable_columns where table_name='V_EMP_SALARY';
--十、索引 应该是把索引和地址放在一个表里面,快速访问
--用来在数据库中加速表查询的数据库对象,通过夸苏路径访问方式快速定位数据,可以有效的减少磁盘I/O操作,提高性能.
--DML操作将会更新索引,增加了dml的时间.
--创建索引的原则:
1.为经常出现在where,order by,distinct子句中的列创建索引,
2.连接条件的列加上索引
3.不要在经常做dml操作的表加索引,不要在小表上面建索引,限制表的索引数目,
1.创建索引
create [unique] index index_name on table(column[,column...]);
create index idx_emp_ename on emp(ename);
select empno, ename, sal, job from emp where ename='SMITH';
2.重构索引 经常做dml操作的表,需要定期重建索引
alter index idx_emp_ename rebuild;
3.删除索引
drop index idx_emp_ename;
4.在数据字典中查看索引 user_indexes, user_ind_columns;
select index_name from user_indexes where table_name='EMP';
十一、序列
用来生成唯一序列值的数据库对象,一般用来自动生成表的主键,
--创建序列
create sequence [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个数据在缓存中,以提高序列的生成效率
--修改序列
alter sequence [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 public synonym synonym_name for [schema.]object;
create public synonym public_emp for emp;
select * from public_emp;
2.创建私有的同义词 只用当前用户可以直接使用,其它用户访问需要该同义词对应表的访问权限
create synonym private_emp for emp;
select * from private_emp;
3.删除同义词
drop public synonym public_emp;
drop synonym private_emp;
4.数据字典中查看
user_synonym;
--参考书籍Oracle 11g数据库编程与实践 宁丽娟
