数据库对象的创建和管理
时间：2021-07-01 10:21:17
帮助过：8人阅读
数据库对象的创建和管理 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数据库编程与实践 宁丽娟
数据库对象的创建和管理
标签：rtm trunc min mp3 oracle delete 递增 个数 ons
