ORACLE: DDL及约束条件

目录

一、基础知识

数据库对象

数据类型

字符

数字

日期

大对象

oracle中的表（表的类型）

查询数据字典

 二、DDL

create

默认值

使用子查询建表

 alter

 drop

三、补充

对象重命名

truncate截断表

四、约束

not null

unique

primary key

foreign key

check

---

一、基础知识

数据库对象

• 表-table：基本的数据存储集合，列组成表的结构，行组成表的数据
• 视图-view：从一张或多张表取出的逻辑相关的数据集合，存储在数据字典中的一条 select 语句
• 序列-sequence：生成规律的数值，：一种生成唯一数字的结构：有序的发出数字
• 索引-index：可以减少对表中行的访问次数、提高查询性能
• 别名（同义词）-synonym：给对象起别名

 对象命名规则

• 必须以字母开头，必须在1-30个字符内；
• 必须只能包含A-Z，a-z，0-9，_，$，#；
• 不能和用户其他定义的对象重名；
• 小写字母会自动转换为大写字母；
• 通过将名称括在双引号内，除长度外其他规则可以打破。但后面使用对象时也一定要用双引号指定；
• 不能为oracle保留字。

查看oracle保留字，在sys用户下：

select * from v$reserved_words where reserved='Y';

数据类型

字符

char(n):

• 固定长度 字符数据，读取效率快，存储时使用 空格 填满空白内容。 n 默认值 1 ，范围 1~2000 字节

varchar2(n):

• 可变长度 字符数据，节省存储空间， n 必须指定 ，范围 1~4000 字节

一个汉字占两个字节

数字

number(p,s)：

• 数值数据，包括零、负数、正数
•  p为precision精度，即总的有效位数。最大38默认38
•  s为scale刻度，即小数点后位数
• s=0:整数，s>0:保留s位小数，s<0:小数点前第|s|位四舍五入，用0取得小数点前|s|位

日期

date：

• 日期和时间值，包括世纪、年、月、日、小时、分、秒
• 必须包含年、月、日 

timestamp(n)：

• 时间戳。比 date 更精准，n 表示秒向下划分的精度范围，n 取值 0~9，默认 6

大对象

BLOB：

• Binary Large Object (二进制大对象)，例如图片、视频、音频

CLOB：

• Character Large Object（字符型大对象），例如文本

BFILE：

• 定位器。指向保存在数据库服务器的操作系统上的文件。文件大小限制为 4GB

oracle中的表（表的类型）

用户表：

• 用户自己创建和维护的表，包含用户的信息

数据字典：数据库服务器创建和维护的一系列表

• 记录数据库信息
• oracle软件维护管理
• 静态数据字典视图
• 动态性能视图：v$

    静态数据字典视图

• uesr_:用户自己拥有的
• all_:用户自己的 + 有权访问的
• dba:数据库所有表 - sys的表

查询数据字典

1、查询用户拥有的表
select * from user_tables;
#只看表名 select table_name from user_tables;
2、查看用户所以的数据类型
select distinct object_type from user_objects;
3、查看用户拥有的表、视图、同义词、序列
select * from user_catalog;

 二、DDL

create

正常创建

create table 表名（col1 数据类型,col2 数据类型，....）

• 必须指定表名、字段名，字段名必须指定数据类型和长度
• 一张表最多创建1000列
• 用户需要有create权限和存储空间

子查询创建

create table 表名  as subquery

默认值

default

可以指定默认值，如：

       create table test (col1 number,col2 date default sysdate);

       insert into test(col1) values(1);

 此时，col2存在默认值为sysdate.

使用子查询建表

1、只创建表结构
create table test as select * from emp where 1=2;
2、不加where 1=2 相当于复制一张表
3、也可以类似这种创建
create table emp20 as select 
empno id,ename name,sal+nvl(comm,0) total_salary 
from emp where deptno=20;

 alter

增加一列：alter table 表名 add（column_name 数据类型）

修改现存列：alter table 表名 modify（column_name 数据类型）

删除列：alter table 表名 drop（column_name 数据类型）

标记列不可用：alter table 表名 set unused（column_name1，column_name2，....）

重命名列：alter table 表名 rename column old_name  to new_name

只读：alter table 表名 read only；

读写：alter table 表名 read write；

注意：

• 标记不可用一般用在数据高业务量高时，无法直接drop，将列不可用，待业务量低时再drop
• 只读表可以drop，但不能DML，也不能截断truncate
• set unused的列可以添加同名的列
• 删除列和标记不可用都是不可逆的。

 drop

drop table 表名

• 操作不能回滚
• 所以未提交事务都将提交
• 表的数据、结构已经表的索引全被删除

三、补充

对象重命名

rename old_table_name to new_table_name;

truncate截断表

truncate table 表名

• 从表中删除所以行，保留表结构
• 属于DDL，不是DML，所以不能rollback
• delete属于DML，可以ROLLBACK，truncate速度很快。

四、约束

 五种约束：

• primary key：主键（非空且唯一）
• unique：唯一（可为空）
• not null：非空
• foreign key：外键
• check：检查

约束存储在数据字典USER_CONSTRAINTS中

查询数据字典中的约束
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,R_CONSTRAINT_NAME from user_constraints;
or
select * from user_cons_columns;

not null

not null只能定义列级，不能定义表级
create table emp01( empno number(4), 
#自定义约束名
ename varchar2(10) constraint nn_emp01_ename not null,  
#按系统约束名
deptno number(7,2) not null);

注意：

• 子查询建表时not null会被继承，但是主键、唯一建、外键均不会被继承 

unique

create table dept01( deptno number(2) unique, 
dname varchar2(14), loc varchar2(13), 
constraint uk_dept01_dname unique (dname));
#有唯一键约束的列不可以插入重复的值，但可以插入一个或多个空值。

primary key

primary key=not null+unique
create table emp01( empno number(4) primary key, 
ename varchar2(10) constraint nn_emp01_ename not null,  
deptno number(7,2) not null);

foreign key

如：emp 表中插入数据时，deptno（员工部门号） 必须是存在于父表dept的deptno列中
create table emp02( empno number(4), ename varchar2(10) not null, 
 deptno number(7,2) not null, 
constraint fk_emp02_deptno foreign key (deptno) references dept02 (deptno) );

外键约束：

• on delete cascade：父表中的列被删除时子表中的列也被删除
• on delete set null： 父表中的列被删除时子表中的列被置为空

check

create table dept03(deptno number(2) constraint ck_dept03_deptno check ( deptno between 10 and 99));